Forum Discussion

Bunta's avatar
Bunta
Frequent Visitor
9 years ago
Solved

Can't get previous months data

Hi all,

 

I'm trying to get the previous months data from the table I have to put alongside the current months but the calculations I'm doing are not returning the results I expect.

 

If I use 

 

available_count_LM = calculate(sum(FINAL[available]), PREVIOUSMONTH(DATES[report_period_end].[Date]))

Then no data is returned in the last month column.

 

 

If I use 

 

available_count_LM = calculate(sum(FINAL[available]), PARALLELPERIOD(DATES[report_period_end].[Date], -1, MONTH))

Then the same values for the current month are returned for last month. 

 

Dummy data:

 

FINAL table:

 

report_period_end,place,available,ReportingPeriod
31/10/2016,1,16382086,1617 M7
30/11/2016,1,16090055,1617 M8
31/12/2016,1,15746408,1617 M9
31/10/2016,2,15966152,1617 M7
30/11/2016,2,16249744,1617 M8
31/12/2016,2,16127134,1617 M9
31/10/2016,3,16143120,1617 M7
30/11/2016,3,15914663,1617 M8
31/12/2016,3,15941826,1617 M9

 

DATES Table:

 

ReportingPeriod,report_period_start,report_period_end
1617 M7,01/10/2016,31/10/2016
1617 M8,01/11/2016,30/11/2016
1617 M9,01/12/2016,31/12/2016

 

Thanks

Need Help
  • SqlJason's avatar
    SqlJason
    9 years ago

    Can you ensure the following is true?

     

    When using date tables to invoke time intelligence in DAX there are three fundamental principles that must always be applied.

    • The date range must be continuous in the Date table. That is there must not be any dates missing in the column that contains the list of calendar days in the table of dates.
    • The date range in the Date table must encompass all the dates that you will be using in other tables in the data model.
    • For time intelligence in Power BI Desktop to work correctly the fields used to join a date table and a data table must both be set to the date or datetime data type.

2 Replies

  • SqlJason's avatar
    SqlJason
    Memorable Member
    9 years ago

    Can you ensure the following is true?

     

    When using date tables to invoke time intelligence in DAX there are three fundamental principles that must always be applied.

    • The date range must be continuous in the Date table. That is there must not be any dates missing in the column that contains the list of calendar days in the table of dates.
    • The date range in the Date table must encompass all the dates that you will be using in other tables in the data model.
    • For time intelligence in Power BI Desktop to work correctly the fields used to join a date table and a data table must both be set to the date or datetime data type.
    • Bunta's avatar
      Bunta
      Frequent Visitor
      9 years ago

      Hi Jason,

       

      Thanks for your response. The second two are true. The first isn't. I was using the distinct dates from my table as the only ones I use are the ones at the end of the month and so only 12 dates in a year rather than having to create a full date table to cover all years etc.

       

      Thanks for answering and I'll guess I'll just create one.

       

      Much appreciated.