Forum Discussion
Power BI DAX Previous month data returns null
I have 2 measure created in SiteUsage table
TotalData = AVERAGEX(SiteUsage ,SiteUsage [Storage_Used_GB]) and
another measure to calculate previous month data
Last_Month_Data = CALCULATE([TotalData],PREVIOUSMONTH(Calendar_Table[Date]))
I have created a custom date table and form [1 to Many] relationship with SiteUsage table as well
Still the Last_Month_Data returns the null data why?
relationships based on same format Date/Time columns
definition of calendar table
Calendar_Table =
VAR
_startDate = EDATE(TODAY(),-60)
VAR
_endDate = EDATE(TODAY(),60)
Return
ADDCOLUMNS(
CALENDAR(_startDate, _endDate)
,"Year",YEAR([Date])
,"Month",FORMAT([Date],"mmm" )
,"WeekNO",WEEKNUM([Date],2))
HI Anonymous ,
You just didnt have any date selected, so it wasnt showing previous month.
Similar error refer:
https://community.powerbi.com/t5/Desktop/Previous-Month-Showing-Blank/m-p/774998
https://community.powerbi.com/t5/Desktop/Why-PREVIOUSMONTH-shows-blank-values/m-p/521219
And when you selected:
Also you could use the below to get what you want:
test = CALCULATE([TotalData],filter(ALL(SiteUsage),FORMAT(SiteUsage[Date],"yyyymm")= format(EOMONTH(MAX(SiteUsage[Date]),-1),"YYYYMM")Output:
Did I answer your question? Mark my post as a solution!
Best RegardsLucien
2 Replies
- AnonymousNot applicable
amitchandak : Could you please look into this as null data is being returned across the complete measure.
- v-luwang-msft
Community Support
HI Anonymous ,
You just didnt have any date selected, so it wasnt showing previous month.
Similar error refer:
https://community.powerbi.com/t5/Desktop/Previous-Month-Showing-Blank/m-p/774998
https://community.powerbi.com/t5/Desktop/Why-PREVIOUSMONTH-shows-blank-values/m-p/521219
And when you selected:
Also you could use the below to get what you want:
test = CALCULATE([TotalData],filter(ALL(SiteUsage),FORMAT(SiteUsage[Date],"yyyymm")= format(EOMONTH(MAX(SiteUsage[Date]),-1),"YYYYMM")Output:
Did I answer your question? Mark my post as a solution!
Best RegardsLucien