Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic last December previous year with DAX

I'm rather new with the DAX formulas and I had a bit of trouble to resolve this issue in SSAS Tabular recently. I did solve it but I am wondering if it was the right way to do it?

 

My customer wanted a measure that would show the outcome of their projects. The measure should show the outcome from the project start date to December 31 PREVIOUS year. And it had to be dynamic since we don't know what year last December is. The customer wanted a pivot table in Excel to show the measure.

 

I tried a lot of DAX-date formulas but I didn't get it to work. But eventually I ended up with the following solution: I created a hidden measure called "LastPeriodPreviousYear" and it looked like this:

 LastPeriodPreviousYear:= LASTDATE(PREVIOUSYEAR(Period[Month_Date]))

Then I created a new measure that used LastPeriodPreviousYear, and it look like this:

Outcome PTD last year:= CALCULATE ( SUM ( MOD001[Amount] ); DATESBETWEEN ( Period[Month_Date]; BLANK (); [LastPeriodPreviousYear] ) )

These two calculations gave me the result that I wanted, and the customer is satisfied. But! I am curious and I want to learn. Is there a simpler/better/more good looking way to solve this matter? Thanks in advance!

8 Replies

  • Anonymous , You get in the following way last year-end date for standard calendar

     

    last year end date = Date(year(today()),12,31)

    last year end date = Date(year(today()),12,31)

    last year end date = Date(year(eomonth(Period[Month_Date],0)),12,31)

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak Thanks for your reply. I've tried to use those formulas but I ended up with this error: "Error: Calculation error in measure 'MOD001'[Testa]: A single value for column 'Month_Date' in table 'Period' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

      Do you have any thoughts about this?

       

      Thank you.

      /Peps

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion

        Anonymous - The error you are getting seems to indicate that you need to wrap any column references you have in the formula with an aggregation like MAX, MIN, etc.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    Anonymous That's a decent solution using Time "Intelligence". If you ever need a non-TI solution, you may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

    Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

    https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

    • Anonymous's avatar
      Anonymous
      Not applicable

      Greg_Deckler Think you nailed your description of the DAX Time intelligence! 😂