Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]
@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
Hi @amitchandak
I have created a table calendar using
Connections made. Filter on Filter and Date on Date
@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
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.
User | Count |
---|---|
53 | |
35 | |
20 | |
15 | |
14 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |