Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I was reading one of your previous answers (Use the bar chart to show the year-over-year comparison 08-19-2020)
and unable to fully understand what you are doing. Your solution is below
You can use the following formula:
monthColumn = YearMonth[A]. [Month]
yearColumn = YEAR ( 'YearMonth'[A] )
I am trying to replicate these, but not sure if they are Measures or Columns. I have a Calendar table and am replacing
'Calendar'[Date] with your YearMonth[A]. But can't get it to work like you did. Can you provide additional detail?
I am trying to replicate the chart below. But this was done in a haphazard manner using multiple tables and Measures. I need to simplify my data model. This is basically same as your solution, but I am using line chart instead.
Appreciate any insight you can provide.
Thanks and Best Regards
Solved! Go to Solution.
Hi @rsbin ,
Based on your description, you can create a measure as follows:
Value =
CALCULATE(SUM(CASE2[B]),FILTER(ALLEXCEPT(CASE,CASE[yearColumn]),'CASE'[monthColumn1]<=MAX('CASE'[monthColumn1])))
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, I think this works. Apologies for the delay in closing this one off. I had thought I had done that.
Thanks again and best regards,
Hi @rsbin ,
Based on your description, you can create a measure as follows:
Value =
CALCULATE(SUM(CASE2[B]),FILTER(ALLEXCEPT(CASE,CASE[yearColumn]),'CASE'[monthColumn1]<=MAX('CASE'[monthColumn1])))
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks much for the reply. I will give it a shot and let you know how I make out. From a quick glance at the image, it does look like that's what I want.
Thanks again and Best Regards,
Hi @rsbin ,
@rsbin , With date table and month-year on axis you should able compare month or YTD
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
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"))
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 :radacad sqlbi My Video Series Appreciate your Kudos.
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Hello @amitchandak
Thanks so much for the reply. I have seen you post these formulas in the past, but I am unable to get them to work in combination. My expected end result is:
I have volume data by month. In 2019, I have data that starts in June til year end. In 2020, I have volume by month from Jan to present. My Calendar table starts at 01/01/2019 til 12/31/2020 and I have linked the two Date Columns.
Using your YTD Sales (2020) from above, I get the following which is fine and dandy.
However, when I use the Dax for "LastYearSales" and bring it into the visual, it gives me an error about expecting a contiguous selection......
I just can't seem to be able to get the result I want from one Fact table. Any further insights would of course be much appreciated!
As always, Kind Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |