locked
DAX Year over Year in line chart RRS feed

  • Question

  • I'm trying to write a DAX expression for calculating the year over year in a line chart.  The problem is that my DAX expression is resulting in a flat line and not calculating it as one day's payment amount over the TOTAL payment amount.  e.g. In this example, I'm only trying to display only one month.  When I use a Date column as my Axis, it displays correctly as below (CRD).  But when I use a Text column representing the day, it displays a flat line.  (My code for CRD Day is FORMAT([CRD],"dd")).

    I also created these two measures and displayed them in this table which shows that it is not using the TOTAL payment amount for the denominator.

    MTD RT % = [MTD Running Total] / [Total Payment Amount]
    MTD RT2 % = CALCULATE(SUMX(VALUES(Cash_Goals_Cash_Velocity[CRD]), [MTD Running Total] / [Total Payment Amount]),ALL(Cash_Goals_Cash_Velocity[net_payment_amount]))
    Total Payment Amount = CALCULATE(SUM(Cash_Goals_Cash_Velocity[net_payment_amount]),ALL(Cash_Goals_Cash_Velocity[CRD]))
    MTD Running Total = TOTALMTD(SUM([net_payment_amount]),Cash_Goals_Cash_Velocity[CRD])

    This table looks like below.  What am I doing wrong?  How can I do this in Power BI?



    Ryan D


    • Edited by ironryan77 Friday, February 3, 2017 4:06 PM
    Friday, February 3, 2017 3:54 PM

Answers

  • Thank you all for your tips.  But I need to make this a running total.  This was the DAX script that worked for me:

    MTD RT 2015 % = SUMX(SUMMARIZE(Cash_Goals_Cash_Velocity,[CRD Day2]),
    CALCULATE([MTD Running Total] / [Total Payment Amount],ALLSELECTED(Cash_Goals_Cash_Velocity[CRD Day2]),Cash_Goals_Cash_Velocity[CRD_YrMonth]=201501))


    Ryan D

    • Marked as answer by ironryan77 Monday, February 6, 2017 3:00 PM
    Monday, February 6, 2017 3:00 PM

All replies

  • It seems you dont have a calendar table. Create a calendar table, join it and use the calendar columns instead and i think you will find it will work. http://exceleratorbi.com.au/power-pivot-calendar-tables/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, February 5, 2017 11:28 AM
    Answerer
  • Hi ironryan77, 

    Please list the “Total Payment Amount” in the table, can check if it calculates all the Cash_Goals_Cash_Velocity[net_payment_amount] values and is same?

    In your second MTD RT2 %, you use the filter of “ALL(Cash_Goals_Cash_Velocity[CRD]”, the “Total Payment Amount” and “MTD Running Total” get the same result, so MTD RT2 % returns 1.0 for all days. 

    In addition, TOTALMTD (Expression, Date_Column [, SetFilter]) works in a date dimension, which must have contiguous, nonrepeating dates from January 1 of the first year you have data to December 31 of the last year you have data. The result of using TOTALMTD  should be running total. Form the table shown, it is incorrect, please create a calendar table using calendar function as the given above. 


    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 6, 2017 6:57 AM
  • Thank you all for your tips.  But I need to make this a running total.  This was the DAX script that worked for me:

    MTD RT 2015 % = SUMX(SUMMARIZE(Cash_Goals_Cash_Velocity,[CRD Day2]),
    CALCULATE([MTD Running Total] / [Total Payment Amount],ALLSELECTED(Cash_Goals_Cash_Velocity[CRD Day2]),Cash_Goals_Cash_Velocity[CRD_YrMonth]=201501))


    Ryan D

    • Marked as answer by ironryan77 Monday, February 6, 2017 3:00 PM
    Monday, February 6, 2017 3:00 PM