Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rsbin
Super User
Super User

Creating Month Axis for Yearly Comparisons

Hello @v-eqin-msft,

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
Super User
Super User

@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.

@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,

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
Super User

@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:

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.