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
kozlevchar
Resolver I
Resolver I

YTD formula for Previous Year with sum Total by Monthly Slicer

Hello,

 

This has been quite an education in DAX!  Having trouble creating a formula for previous year YTD with sum totals by monthly slicer.  Here's the one that works for CURRENT YEAR:

 

CC YTD 2019 V4 = CALCULATE(
SUMX('QA Data','QA Data'[Consumer Complaints 2019]) , FILTER(ALL('Calendar') , 'Calendar'[Date].[Year] <= MAX('Calendar'[Date].[Year])))

 

If I use the above formula for the previous year (2018) it returns the full year thru December.  If I remove the ALL function it will only give me monthly numbers and TOTALS not YTD.  

 

How can this formula be modified to give YTD up to the month in a date slicer?  Any suggestion would be greatly appreciated.

 

Warmest Regards,

 

John

 

1 ACCEPTED SOLUTION

After reading and studying Maggie's work I realized that her work was referenceing a date column while mine references a text.  I looked at an older formula I created and changed it to the following:

 

CC YTD 2018 = IF(HASONEVALUE('QA Data'[Group]) ,
    CALCULATE(SUMX('QA Data','QA Data'[Total Complaints for 2018]), DATEADD(FILTER(DATESYTD('Calendar'[Date]) , 'Calendar'[Date]) , -1, YEAR)) ,
    CALCULATE(SUMX('QA Data','QA Data'[Total Complaints for 2018]), DATEADD(FILTER(DATESYTD('Calendar'[Date]) , 'Calendar'[Date]) , -2, YEAR)))
 
I changed the HASONEVALUE Column name to 'QA Data'[Group]  from the Date column which solved the SUM total.
 
Thanks again Maggie,
 
John K

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

Hi @kozlevchar 

For this example,which YTD is your expected one?

If neither of them is, please give the expected result based on this example.

1.png

Best Regards

Maggie

Hi Maggie,

 

Thanks for the reply.  It is YTD3.  I'm attempting to get this result without using a Yearly Slicer.  Only Monthly.  I believe this is why it is proving so hard.  Trying to input the year into the formula. 

 

Best Regards

 

John

Hi @kozlevchar 

If you just need to calculate the previous year's YTD, you could enter previous year in the formula.

In my solution, 

First, i create a new table, please note that i don't connect this table to any other table.

date slicer = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

I add "month" column from this table in a slicer,

 

Second, create measures in you main data table (Called "Sheet1" in my test)

sales-measure = SUM(Sheet1[sales])

selected month = SELECTEDVALUE('date slicer'[month])

meet condition = 
IF(MAX('calendar'[year])=YEAR(TODAY())-1&&MAX('calendar'[month])<=[selected month],1,0)

YTD2 = SUMX(FILTER(Sheet1,[meet condition]=1),[sales-measure])

YTD3 = 
IF([YTD2]=BLANK(),BLANK(), SUMX(FILTER(ALL(Sheet1),Sheet1[date]<=MAX(Sheet1[date])),[YTD2]))

In [meet condition] measure, "YEAR(TODAY())-1" means previous year.

1.png

Please download my pbix filr to see more details.

 

Best Regards

Maggie

 

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

Hi Maggie,

Thanks so much for your time and attention to this problem.  Apologies for the delay.  Really some great work...When I create the table and

 

measures requested I get an Value Error.PNG

 

error.  I'm using the Date Slicer I created.  The month is an integer.  My Rows are not dates.  My Rows are text. 

Tried a number of changes but can't get rid of this error.  Any ideas would be helpful.  This is really some great work if we can get around this issue.

 

Thanks again,

 

John

 

 

After reading and studying Maggie's work I realized that her work was referenceing a date column while mine references a text.  I looked at an older formula I created and changed it to the following:

 

CC YTD 2018 = IF(HASONEVALUE('QA Data'[Group]) ,
    CALCULATE(SUMX('QA Data','QA Data'[Total Complaints for 2018]), DATEADD(FILTER(DATESYTD('Calendar'[Date]) , 'Calendar'[Date]) , -1, YEAR)) ,
    CALCULATE(SUMX('QA Data','QA Data'[Total Complaints for 2018]), DATEADD(FILTER(DATESYTD('Calendar'[Date]) , 'Calendar'[Date]) , -2, YEAR)))
 
I changed the HASONEVALUE Column name to 'QA Data'[Group]  from the Date column which solved the SUM total.
 
Thanks again Maggie,
 
John K
kozlevchar
Resolver I
Resolver I

PBI QA 1.PNG

 

I inserted a DATEADD...The following formula gives me the correct numbers (Last Year) in the column but the TOTAL for the column is from 2019.  SEE ABOVE

 

CC YTD 2018 V3 = CALCULATE(SUM('QA Data'[Total Complaints]), DATEADD(FILTER(DATESYTD('Calendar'[Date]) , 'Calendar'[Date]) , -1, YEAR))

 

So now there's 2 formulas to work with.  Will post again if I figure it out but any help would be greatly appreciated.

 

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.