Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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 8
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

amitchandak
Super User
Super User

@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
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

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler Thanks for your reply. 

I've been looking in to that, and I don't think that it's about MIN, MAX stuff, but rather that my time-dimension is a bit wrong. We don't have any real dates, just a month-date like 2020-09-01 and that's september. And the next month-date is 2020-10-01, so there is no real regularity to our dates.

 

Or mayby that the formula needs a more specific date or something? Do you have any thoughts about that?

 

I'm sorry for the lack of information, but as I said from the beginning, I'm a newbie at DAX (don't really get it, it's a **bleep**ty kind of language....) and I'm not great at Excel for that matter so I have a bit of trouble understanding how it works.

 

But thank you for taking the time mate!

@Anonymous The error you are getting indicates that a table of values is being returned when it expects a scalar. This means you are missing an aggregation somewhere. Easiest way to get around that many times is just wrap you column reference in an aggregation like MAX.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Have been trying some options, and I thank you for you input. @amitchandak I gave it shot, but it didn't really work. How ever, this seemed to work: 

LastPeriodPreviousYear:= Date(year(MAX(Period[Month_Date]),0)-1,12,31) 

Thank you so much for your input @Greg_Deckler  and @amitchandak  

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors