Calculate YTD data while having monthly and weekly filters

[Betobri]

Hello everyone,

I'm trying to make a scorecard report on Power BI, which gives me the weekly, MTD and YTD information about plant production, but i'm having trouble getting the correct YTD information.

I have a separate calendar table which connects to 5 different data sources by a Date Key column: 

Date key = 'Plant Scorecard - Conformance'[Year]&"-"&'Plant Scorecard - Conformance'[Month]&"-"&'Plant Scorecard - Conformance'[Week]

I don't have a date column on these datasource, that's why i made the Date Key.

This is the connections i have are made this way:

Connections

My data table looks like this:

Table

What i need to do is to show the Last Week, Month-To-Date and Year-To-Date information, and being able to filter it by Month and week and the data to be dynamic.

My main problem is that everytime i try to do the YTD formula, it keeps giving me the Last Week information, instead of the YTD, like the data was being filtered and it's not showing me the accumulative data since the start of the year.

The DAX formula i used to calculate the YTD information was this:

CALCULATE([Eff %],DATESYTD('Production Analysis'[Week End].[Date]))

Do i need to change my connections or do something different? Or do i need to use another formula in order to make it work? The MTD measure is not giving any problems, so i don't know how can i make it work for the YTD.

I'd really appreciate any help you guys could give me, i'm fairly new to Power BI so in case wasn't clear enough i'll try to explain it in a reply.

Thanks in advance.

[amitchandak]

Seem like you have a date calendar. And I am assuming you have Month, year, week defined there.

For week use rank and find last week. Refer this file

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

For YTD and MTD use total* or dates* time intelligence functions

examples

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))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


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")))

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

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/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))

[Betobri]

With Week and MTD i'm not having any problems, and i'm using the exact formula for YTD as you've mentioned.

My Date table does have a Week, Month and Year columns, but they're numbers.

Dates

I'm still having problems even with your formula.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[amitchandak]

How you have joined these two tables. Date table should have all dates And if you have data at week level you should be joined Week end or start date.

In dateytd you have to pass the calendar date which is marked at date or is the join date

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[amitchandak]

@Betobri ,Does the above replies helps. if you need more help make me @

Appreciate your Kudos.