I

[pradiptasaha]

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.

[amitchandak]

@pradiptasaha , 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.

[pradiptasaha]

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.