Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

I

I have monthly data on a set of KPIs, and want to create a table that gives the value of each KPI for the latest period as well as the value in the previous period (last month). 

 

KPI     AsOF  Value

A          8/31    99

A           7/31   88

B          8/31    2.5

B          7/31    2.9

 

I want the output table to look like this

KPI      Current MOnth     Prev Month

A                 99                      88

B                 2.5                      2.9

 

I wrote a measure for Current Month

 

calculate(sum(Data[Value]),filter(Data,Data[As Of]=max(Data[As Of])))
 
this works fine.
 
I am unable to figure out the measure for previous month. Max - 1 works, but that will obviously fail in January and when the data spans multiple years.

    

 

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Create a date table join it with AsOF 

     

    Try measures like

    MTD = CALCULATE(SUM(Table[Value]),DATESMTD('Date'[Date]))
    last MTD = CALCULATE(SUM(Table[Value]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month = CALCULATE(SUM(Table[Value]),previousmonth('Date'[Date]))
    this month = CALCULATE(SUM(Table[Value]),DATESMTD(ENDOFMONTH('Date'[Date])))
    last MTD (complete) = CALCULATE(SUM(Table[Value]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
    previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

    diff = [MTD]-[last MTD]
    diff % = divide([MTD]-[last MTD],[last MTD])

     

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks so much. This didnt exactly solve my problem, but much more importantly led me to a better understanding of date dimensions in DAX and how to think about the data model itself. I have made the monthly measures work with some reworking of the data tables, with one remaining challenge that is coming from the fact that some of my facts are captured monthly, some quarterly, and when I use a QTD, the months get summed for the monthly facts as well. Is there a way to suppress the calculation for the metrics that are monthly?

       I am using 

      cq = CALCULATE(SUM(facts[For Period]),DATESQTD(ENDOFQUARTER(Dates[Date])))
       
      I dont want to calculate / show cq for fact1 and fact2.