cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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.

6 REPLIES 6
Super User

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,

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

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.

Super User

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,

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

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

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:

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,