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 Va...

amitchandak

Super 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

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.

Forum Discussion

I