cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Post Patron
Post Patron

Creating Month Axis for Yearly Comparisons

Hello @Eyelyn9,

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

1 ACCEPTED SOLUTION
v-yuaj-msft
Community Support
Community Support

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

 

v-yuaj-msft_0-1604971947996.png

 

Result:

v-yuaj-msft_1-1604971948003.png

 

 

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.

View solution in original post

6 REPLIES 6
rsbin
Post Patron
Post Patron

@v-yuaj-msft,

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,

v-yuaj-msft
Community Support
Community Support

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

 

v-yuaj-msft_0-1604971947996.png

 

Result:

v-yuaj-msft_1-1604971948003.png

 

 

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.

View solution in original post

@v-yuaj-msft 

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,

v-yuaj-msft
Community Support
Community Support

Hi @rsbin ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem.
 
Best Regards,
Yuna



amitchandak
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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:

rsbin_0-1604498869427.png

 

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.

rsbin_1-1604499163082.png

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,

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors