YTD and YOY / Help with formulas

[Anonymous]

Hi everyone.

I need help with 4 measures to build a matrix with Month Value, YTD Prior Year, YTD Current Year, YOY Development.

My slicers have all 3 Coding tables and both Year and Month Slicers are single selections

For the month value it's easy

[Month Value] = Divide(Compliant,Total,0)

Now, i have searched in here and on the web i couldn't find any solution that suites my request.

For the [YTD Prio Year] = Divide( SUM(Compliant) , SUM(Total) , 0 ) but till the month selected on the slicer and if 2021 is selected this one should calculate for 2020.

For the [YTD Current Year] = Divide( SUM(Compliant) , SUM(Total) , 0 ) but till the month selected on the slicer and if 2021 is selected then YTD based on the 2021.

And last [YoY Development] = [YTD Current Year] - [YTD Prio Year]

Thank you in advance to anyone that can help me with these.

Bellow you can find the structure of my data.

My data table is called TBL_Automation and has the following structure

also have 3 Coding tables called:

COD_Calendario

COD_Region

COD_Product

Connections are

COD_Calendar[Filter] -> TBL_Automation[Filter]

COD_Region[Country] -> TBL_Automation[Country]

COD_Product[Procduct] -> TBL_Automation[Indicator]

[amitchandak]

@Anonymous , Create a date table and then try time intelligence

example

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

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[Anonymous]

Hi @amitchandak 

I have created a table calendar using 

COD_Date = CALENDAR( MIN(TBL_Automation1[Filter]), MAX(TBL_Automation1[Filter]) )

Year = YEAR('COD_Date'[Date])

Month = MONTH('COD_Date'[Date])

Filter = 'COD_Date'[Month]&"/"& 'COD_Date'[Year] - Format as Date so i can connect to the COD_Calendar.

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

Last year Sales = CALCULATE(SUM(TBL_Automation1[Compliant]),DATESYTD(ENDOFYEAR(dateadd(COD_Date[Date],-1,Year)),"12/31"))

Last YTD Sales = CALCULATE(SUM(TBL_Automation1[Compliant]),DATESYTD(dateadd(COD_Date[Date],-1,Year),"12/31"))

Last YTD Sales = CALCULATE(SUM(TBL_Automation1[Compliant]),DATESYTD(dateadd(COD_Date[Date],-1,Year),"12/31"))

YTD Sales = CALCULATE(SUM(TBL_Automation1[Compliant]),DATESYTD('COD_Date'[Date],"12/31"))

But the output is always the same for all the measures

Connections made. Filter on Filter and Date on Date

[amitchandak]

@Anonymous , I see date on both side, join should be on date on both side.

Even if you have  the month and year, you should create a date and join

example

date = "01-" & [Month] & "-"& [Year] // Month is Jan or January , change data type to text

or

date = date([year], [month],day)

In M -https://youtu.be/cJqgphIHXz8

in DAX in few mins - https://youtu.be/yPQ9UV37LOU

Make sure date table is marked as the date and you are using month year from date table in visual

refer why Time Intelligence fails https://youtu.be/OBf0rjpp5Hw

[Anonymous]

Hi @amitchandak.

Now I am lost.

Can i do what you suggest but on my COD_Calendar?

And change the Filter to Filter= "01-" & [Month] & "-"& [Year] // Month is Jan or January , change data type to text.

These Cod_Calendar comes from Excel.