Forum Discussion

apuype's avatar
apuype
Frequent Visitor
3 years ago

Previous Period Not Grabbing in Measure

Looking for any assistance - I have date periods set for a date slicer ie "Last 30 Days"

I have created start of this period, end of this period, start of previous period, end of previous period measures so that I can ensure the report is seeing the dates correctly. 

Where my report hits is snag is when I want to do a caluclation on the previous period. IE order count 

Current Period gives me a caluation with the below measure:

 

Count of Orders Current Period =
                          CALCULATE([Count of Orders],
                             DATESBETWEEN(
                               Dim_Date[CalendarDate],
                                 'My Measures'[Start of This Period],'My Measures'[End of this Period]))
 
When I apply the same logic to previous period I get blank

 

Count of Orders Previous Period =
                    CALCULATE([Count of Orders],
                      DATESBETWEEN(
                        Dim_Date[CalendarDate],
                           'My Measures'[Start of Previous Period],'My Measures'[End of Previous Period]))


Any assistance is appreciated!

 

previous period

2 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    3 years ago
    apuype wrote:
    When I apply the same logic to previous period I get blank

     

    This is because your slicer is selecting the current month and your DATESBETWEEN logic is selecting the prior month and these two filters contradict each other. There are no dates that are in both the current month and the prior month. To fix this you can add the REMOVEFILTERS call to your prior month expression to remove the affect of the slicer.

     

    eg

     

    Count of Orders Previous Period =
                        CALCULATE([Count of Orders],
                          REMOVEFILTERS( Dim_Date ) ,
                          DATESBETWEEN(
                            Dim_Date[CalendarDate],
                               'My Measures'[Start of Previous Period],'My Measures'[End of Previous Period]))
    • apuype's avatar
      apuype
      Frequent Visitor
      3 years ago

      Thank you, I was very excited to try that but it is still giving me blank for my previous period

      I did want to add prior to me creating a set date range for my slicer this logic did work as it was supposed to (ie when using the date slider for custom date selection), the prior period comparions broke when this format was added. 

      The date table to use this set up was built by the following - my dim date table then joins to this table as well as my data table

      SpecialDates =
      VAR _datetable = Dim_Date
      VAR _today = TODAY()
      VAR _month = MONTH(TODAY())
      VAR _year = YEAR(TODAY())
      VAR _thismonthstart = DATE(_year,_month,1)
      VAR _thisyearstart = DATE(_year,1,1)
      VAR _lastmonthstart = EDATE(_thismonthstart,-1)
      VAR _lastmonthend = _thismonthstart-1
      VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1)


      RETURN UNION(
      ADDCOLUMNS(FILTER(_datetable,[CalendarDate] =_today),"Period","Today","Order",1),
      ADDCOLUMNS(FILTER(_datetable,[CalendarDate]=_today-1),"Period","Yesterday","Order",2),
      ADDCOLUMNS(FILTER(_datetable,[CalendarDate]>_today-7),"Period","Last 7 Days","Order",3),
      ADDCOLUMNS(FILTER(_datetable,[CalendarDate]>=_thismonthstart),"Period","This Month","Order",4),
      ADDCOLUMNS(FILTER(_datetable,[CalendarDate]>=_thisquarterstart),"Period","This Quarter","Order",5),
      ADDCOLUMNS(FILTER(_datetable,[CalendarDate]>=_thisyearstart),"Period","This Year","Order",6),
      ADDCOLUMNS(FILTER(_datetable,[CalendarDate]>_today-30),"Period","Last 30 Days","Order",7),
      ADDCOLUMNS(_datetable,"Period","Custom...","Order",8)
      )
       

      Thank you for your feedback! If you can see anything else that maybe it causing this issue please let me know.