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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Richard76
Helper II
Helper II

Same period 2 years ago

Hi Folks,

I use the following 2 formulae to get the sales figure up to the same day in the previous year - I now need to get the same figure for 2 years ago - can anyone help ??

 

Total Sales LY = Calculate( [Total Sales], SAMEPERIODLASTYEAR( 'Time'[PK_Date] ))
RunningTotalLY =
CALCULATE(
    [Total Sales LY],
    FILTER(
        ALLSELECTED('Time'[PK_Date]),
        ISONORAFTER('Time'[PK_Date], MAX('Time'[PK_Date]), DESC)
    )
)

 

Many thanks

Richard

1 ACCEPTED SOLUTION

Sorry found a typo, this should work (it did on my dataset atleast):

RunningTotal2LY = 
VAR curYrMinDate  = MIN( 'Time'[PK_Date])
VAR curYrMaxDate  = MAX( 'Time'[PK_Date] )
VAR minDate = DATE( (YEAR( curYrMinDate ) -2 ),MONTH(curYrMinDate), DAY(curYrMinDate))
VAR maxDate = DATE( (YEAR( curYrMaxDate  ) - 2 ), MONTH(curYrMaxDate), DAY(curYrMaxDate))
RETURN
CALCULATE(
    [Total Sales],
    FILTER(
        ALLSELECTED('Time'[PK_Date]),
        'Time'[PK_Date] > minDate && 'Time'[PK_Date] < maxDate
    )
)

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
JarroVGIT
Resident Rockstar
Resident Rockstar

Hi @Richard76 ,

This should do the trick I think (haven't tested it though, hard to create a dataset quickly for this one, apologies);

 

 

RunningTotal2LY = 
VAR curYrMinDate  = MIN( 'Time'[PK_Date] )
VAR curYrMaxDate  = MAX( 'Time'[PK_Date] )
VAR minDate = DATE( YEAR( curYrMinDate ) - 2, MONTH( curYrMinDate ), DAY( curYrMinDate  ))
VAR maxDate = DATE( YEAR( curYrMaxDate  ) - 2, MONTH( curYrMaxDate  ), DAY(curYrMaxDate  ))
CALCULATE(
    SUM([Total Sales],
    FILTER(
        ALLSELECTED('Time'[PK_Date]),
        'Time'[PK_Date] > minDate && 'Time'[PK_Date] < maxDate
    )
)

 

 

Let me know if that works for you!

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Wow impressive ! I've tried copying in but just says 'syntax is incorrect' . Would you be able to talk me through what it is doing so I can try and self diagnose. I've never used VAR before in DAX so not sure what all the formulae is doing. 

 

Thanks for your help .

 

Richard

Whoops forgot to insert RETURN between the last VAR and CALCULATE statement. Typing on phone, sorry.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Still not working but could be something to do with my data .

Will let you know .

 

R

Sorry found a typo, this should work (it did on my dataset atleast):

RunningTotal2LY = 
VAR curYrMinDate  = MIN( 'Time'[PK_Date])
VAR curYrMaxDate  = MAX( 'Time'[PK_Date] )
VAR minDate = DATE( (YEAR( curYrMinDate ) -2 ),MONTH(curYrMinDate), DAY(curYrMinDate))
VAR maxDate = DATE( (YEAR( curYrMaxDate  ) - 2 ), MONTH(curYrMaxDate), DAY(curYrMaxDate))
RETURN
CALCULATE(
    [Total Sales],
    FILTER(
        ALLSELECTED('Time'[PK_Date]),
        'Time'[PK_Date] > minDate && 'Time'[PK_Date] < maxDate
    )
)

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.