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
Anonymous
Not applicable

PARALLELPERIOD for YTD vs LYTD Calculation - DAX Question

I am asking for help regarding my DAX measure for comparing this year-to-date with last year-to-date. Here are my statements: Calendar Table = CALENDAR (MINX(mytable, mytable[mydate]), NOW()) - Works correctly YTD Measure = TOTALYTD(SUM(mytable[myvalue]), 'DateTable'[Date]) - Works correctly LYTD Measure = CALCULATE([YTD Measure],SAMEPERIODLASTYEAR('DateTable'[Date]) - Does not work how I want it to My LYTD Measure calculates until the end of the month. For Example: YTD = 01/01/2019 thru 01/10/2019 LTYD = 01/01/2018 thru 01/31/2018 But I need it to calculate this: YTD = 01/01/2019 thru 01/10/2019 LTYD = 01/01/2018 thru 01/10/2018 Any help would be appreciated. Thanks
1 ACCEPTED SOLUTION

hi, @Anonymous

First, you could try to use another browser like IE, your post will not be messy.

and for your case, SAMEPERIODLASTYEAR and TOTALYTD are Time-intelligence functions. you'd better create a full date table.

You try this way as below:

Step1:

Use CALENDARAUTO Function to create a date table.

Step2:
Add a column for date table to filter date before today.

 

filter = IF(DateTable[Date]<=TODAY(),1,2)

Step3:
Add the field [filter] to report level filter and set filter is 1

5.JPG

Result:

BeforeBeforeAfterAfter

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Sorry about the formatting above. Below is the question formatted correctly: I am asking for help regarding my DAX formula for comparing this year-to-date with last year-to-date. Here are my statements: Calendar Table: = CALENDAR (MINX(mytable, mytable[mydate]), NOW()) - Works Correctly YTD Measure: = TOTALYTD(SUM(mytable[myvalue]), 'DateTable'[Date]) - Works Correctly LYTD Measure: = CALCULATE([YTD Measure],SAMEPERIODLASTYEAR('DateTable'[Date]) - Works Incorrectly My LYTD Measure calculates until the end of the month. For Example: YTD = 01/01/2019 thru 01/10/2019 LTYD = 01/01/2018 thru 01/31/2018 But I need it to calculate this: YTD = 01/01/2019 thru 01/10/2019 LTYD = 01/01/2018 thru 01/10/2018 Any help would be appreciated. Thanks

hi, @Anonymous

First, you could try to use another browser like IE, your post will not be messy.

and for your case, SAMEPERIODLASTYEAR and TOTALYTD are Time-intelligence functions. you'd better create a full date table.

You try this way as below:

Step1:

Use CALENDARAUTO Function to create a date table.

Step2:
Add a column for date table to filter date before today.

 

filter = IF(DateTable[Date]<=TODAY(),1,2)

Step3:
Add the field [filter] to report level filter and set filter is 1

5.JPG

Result:

BeforeBeforeAfterAfter

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Lin,

 

Thanks for the answer and also the help with the formatting. Have a great day.

 

John

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.