Forum Discussion

marktait's avatar
marktait
Helper I
2 years ago
Solved

PreviousMonth not returning any data

Hi.

 

I'm trying to setup a simple table using the PreviousMonth function.

 

The two measures are setup as follows:

 

  • Risk Score Calc = average(Defender[Risk Score - %Total])
  • Risk Score Calc Prev = calculate([Risk Score Calc],PREVIOUSMONTH(Defender[Extract Month]))
My table in PBI is setup and correctly showing the "Risk Score Calc", however the "Risk Score Calc Prev" is not showing any data:
 
 
"Extract Month" is setup as a date:
 
 
Can anyone see why "Risk Score Calc Prev" is not showing any data?
 
Thanks for any help,
 
Mark
 
 
Need Help
  • CoreyP's avatar
    CoreyP
    2 years ago

    Power BI is built so that it best functions when models are in a star schema. Time intelligence functions require this, specifically a date dimension table. You need to create a date dimension table, establish a relationship to your fact table, and adjust your DAX measure to utilize the date table. 

4 Replies

  • CoreyP's avatar
    CoreyP
    Solution Sage
    2 years ago

    Power BI is built so that it best functions when models are in a star schema. Time intelligence functions require this, specifically a date dimension table. You need to create a date dimension table, establish a relationship to your fact table, and adjust your DAX measure to utilize the date table. 

    • marktait's avatar
      marktait
      Helper I
      2 years ago

      Hi Corey - thank you for the suggestion.

       

      I created a date table, which lists all days (monthly), including all dates in my "Defender (facts)" table:

       

       

      I linked them as follows:

       

       

      ..and I changed the calculation to:

      Risk Score Calc Prev = calculate([Risk Score Calc],PREVIOUSMONTH(DateListTable[Dates]))
       
      However, the table still remains the same, with the Prev column unpopulated.
       
      Thanks again for any help,
       
      Mark
      • CoreyP's avatar
        CoreyP
        Solution Sage
        2 years ago

        Date tables need to have a contiguous series of dates. ( Each row is a day, not a month. ) Use the function CALENDAR with FIRSTDATE and LAST DATE to create a dynamic date table with a contiguous set of dates.