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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rsbin
Super User
Super User

CurrentYear - Last Year

Good Afternoon,

It's been a while since I have had to do any DAX.  For some reason, I am getting myself all twisted up with this one.

I have a Measure to calculate [Selling Profit] YTD:

SellingProft_YTD = CALCULATE( [Selling Profit],
                   ALL(DimTime),
                   DATESYTD( DimTime[Date], "12/31" ))          

Unfortunately, I have inherited this model from another developer.
First issue is the company maintains future dated invoices in their Fact Table.  But I only want the Calculation up to and including Today().   When I tried adding this additional filter, I lost my Cumulative Values.  Unsure of the syntax to accomplish this.

 

Second issue is I need to calculate the Difference in [Selling Profit].   So [Selling Profit] Current Year - [Selling Profit] Last Year.

Got myself all twisted trying to do this.  A helping hand would be much appreciated.

 

Thanks and Regards,

 

6 REPLIES 6
Padycosmos
Solution Sage
Solution Sage

@Padycosmos ,

So using the TotalYTD function, I was able to replicate the results I had in my original post.  So I am no further ahead at this point.  Still unable to resolve my first issue of restricting the max date to today.

Any advice?

You may please try this

CY YTD = TOTALYTD([Total Sales],Orders[Order Date],Orders[Order Date]<TODAY())

@Padycosmos ,

Nope, unfortunately, that doesn't work.  I lose my Cumulative Results and it just gives me one number (i.e $3.5M for all dates ).

 

Instead of using the Date column from the Dimtime table, could you please try using the Date from the Fact table?

@Padycosmos ,

Thanks for passing along the video.  Will have a look at it to see if I can get my model working.

Haven't used those functions before, so may take me some time to get up to speed.

Thanks and regards,

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors