PowerPivot: calculate previous year value if date formatted as text

[illusionek]

I am using below measure, which works fine, the date in MonthYear is dd/mm/yyyy. I have got now a challenge where I need to display MonthYear on PivotCharts as MMM-YY

I went to Format Axis -> Number and tried to Add custom format but it doesn't get applied as a matter of fact I am unable to change the formatting at all even if I select some pre-definied options.

So next thing I did was calculated column where I used FORMAT to change MonthYear, which worked well but now my measure doesn't work when I use new calculated column in the chart.

I just cant get my head around how to change below measure to work with new calculated column

=CALCULATE([Actual Inv],DATEADD(qryMerge[MonthYear],-1,YEAR))

 

[Matt Allington]

The correct way to work with dates is to use a date column to connect to a calendar table, then use the calendar table to meet your formatted reporting needs.
Here are some of my articles.

Create calendar Tables in Power BI

Learn how to create a Power BI calendar table using the basic rules and approach to setting it up daily, weekly or monthly.

exceleratorbi.com.au

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

Build a Reusable Calendar Table with Power Query

In this step by step guide you'll learn how to create a Calendar Table using Power Query and save the code to reuse it later.

exceleratorbi.com.au

 

[illusionek]

It has worked with the calendar table indeed. Just for mu benefit would you be able to explain why my initial formula worked without the calendar table but revised one doesn't?

 

[Matt Allington]

You can only display data on rows/columns if that data exists in you model. Excel uses a trick under the hood to generate a basic hidden calendar table to show Months and Years. But it is not a full calendar table. There is no MMM-YY column in the auto calendar table. This "trick" is actually a pain in my view. It confuses people and prevents them from learning how to do it properly. JMO