YTD formula for Previous Year with sum Total by Monthly Slicer

[kozlevchar]

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

[v-juanli-msft]

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.

Best Regards

Maggie

[kozlevchar]

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

[v-juanli-msft]

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.

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.

[kozlevchar]

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

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

[kozlevchar · 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

[kozlevchar]

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