Dax which can change the value of the Subtotals in one Column if it does not appear in the other!

[Anonymous]

Hello,

Is their a Dax Formula which can make my 2019-2020 Column Exclude Accounting periods that are not yet available in current year 2020-2021 so that the same periods are being compared in the totals? I know I can right click on periods 8 through 12 and simply exclude them but I'd like a Dax that can do this automatically. 

Thank you!

[Ashish_Mathur]

Hi,

If the row and column headers of your visual have been dragged from the Calendar Table and the Calendar Table has dates only till the last day available on the source tble, then try this measure

=if(isblank(calculate([measure],nextyear(calendar[date]))),blank(),[measure])

Does this help?

[Anonymous]

I like this approach as well but my current year data disappears and only my previous year show up. Yes both my accounting period and Fiscal Year come from the Calendar, and my Calander end date is built from the last data load. I should have posted my Visualazations and fields.  In your formula what should go in my first [measure] and what should go in my next [measure]? 

[Ashish_Mathur]

Hi,

[Total amount] as shown in Fowmy's image.

[Fowmy · Accepted solution]

@Anonymous 

Create the following measure and apply.

There a video from sqlbi.com that solves the same problem in a different way:
 https://www.youtube.com/watch?v=bAOs5LTP0I0

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn