Need Help With Monthly Total Vs YTD

[Anonymous]

Hello All,

I have a data set from an access date base that looks like the follow:

Month                                                   Total Monthly X
11/14/2019                                              28
1/14/2020                                                21

This data is also linked to a datetable that I created for time intelliengence related Calculations. What I am seeking to show is the monthly total and YTD. I have set up FY and Month Name as filters, so forexample if I place this in a card visual it should look like this when I filter by Month name and FY:

Month           YTD
21                  49

The challenge i have is that When I place the total monthlyX in card visual, it gives me the same values for both Month Total and YTD such as 49 for both.

Any ideas on how to correct this.

Thanks all for your help

[v-juanli-msft]

Hi @Anonymous 

so forexample if I place this in a card visual it should look like this when I filter by Month name and FY:

Month           YTD
21                  49

which items do you select from the slicer?

Is the YTD is from the start of year 2019 to the max date in the table?

Or YTD is the start date of 2019 and the end date(2019/12/31) of 2019?

Best Regards

Maggie

[amitchandak]

If you are still facing the issue. Please find some formula's that can help

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
 


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  


Rolling 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],maxx(sales,Sales[Sales Date]),-30,DAY))   
Rolling last 30 before 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],(dateadd('Date'[Date],-30,DAY)),-30,DAY))  

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[VasTg]

@Anonymous 

What is the DAX that you used for  YTD measure?

Refer to documentation for TOTALYTD.

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

If this helps, mark it as a solution.

Kudos are nice too

[Anonymous]

@VasTg ,

This is the YTD formula I used: 

[VasTg]

@Anonymous 

Based on the input data you should get 21 for 2020. Could you please post sample data and DAX you used to calculate the YTD and monthly measure and the expected output to better answer your question?

[Anonymous]

@VasTg  Many thanks fro your help thus far.

Those are the only two pieces of information in the data as previoulsy posted. I picked the monthly total from the main table since its already aggregated(value).

YTD dax is as follows: Calculate(Sum(Tablename[monthly value]),Filter(All(Datetable[date]),datetable[date]<=Max(Datetable[Date])).

Expected output when FY and Monthname is applied:

Month                YTD
21                          49

[VasTg]

@Anonymous 

You stated the problem is with Month value is not showing for the correct month. I tested with your input data and I see the correct value. Could you upload your PBIX file to check what the error is.

If it helps, mark it as a solution

Kudos are nice too.

[Anonymous]

@VasTg ,

Did you link it to a datetable? I have my data set up to a datetable. Due to the privacy of the data model, I cannot my pbix. However, I have reviewed your sample but i see you have Calendar year. I have a FY starting from Jul-Jun. And somehow when I place the total monthly into the cad visual, it automatically adds both months just as the YTD.

[VasTg]

@Anonymous 

I have a relationship between the date and fact based on the date column.

Of course it will add when you choose FY. You should filter for month.

[Anonymous]

@VasTg ,

I did filter for month but they both remain the same 49.

I amnot sure why this is going on

[Anonymous]

@VasTg Can you share your pbix file to compare with mine.

I ahve tried different formulas to no avail.

Thanks