Ignore Value in column for visual

[Anonymous]

Hello, not really sure how to explain this but I'm having an issue where I need the visual to ignore the current month (CM) value in 2020 which has both "CM" in January & December I need it to ignore "CM" in January but I still need it there for the measure to calculate correctly.

Cause what's happening now is that we have reports which show 2020 data where the current month should be December and we got others for 2021 where the current month should be January. And having 2 "CM" in the same year breaks the measure which is why I need it to ignore "CM" in January when 2020 is selected. 
 

The measure in question looks like this 

var varAcqTO = "cb Acq TO"
var varBaseTurnover = "cb Base Turnover"
var varTurnover = "cb Turnover"
var varDispTO = "cb Disp TO"
return 
SWITCH( [valTimeIntelligence], 

    "CFC", DIVIDE( (Calculate(SUM('Data'[Value]), 'Data'[FC flag]="Actuals" || 'Data'[FC Flag]="Current FC", 'Data'[Measure Code] = varTurnover)
                    +
                    Calculate(SUM('Data'[Value]), 'Data'[FC flag]="Actuals" || 'Data'[FC Flag]="Current FC", 'Data'[Measure Code] = varAcqTO)
                    )
                    -
                    (Calculate(SUM('Data'[Value]), DATEADD('Calendar'[Date],-1,YEAR),'Data'[FC Flag]="Actuals" || 'Data'[FC Flag]="Current FC", 'Data'[Measure Code] = varTurnover)
                    +
                    Calculate(SUM('Data'[Value]), 'Data'[FC flag]="Actuals" || 'Data'[FC Flag]="Current FC", 'Data'[Measure Code] = varDispTO)
                    ),
                    (Calculate(SUM('Data'[Value]), DATEADD('Calendar'[Date],-1,YEAR),'Data'[FC Flag]="Actuals" || 'Data'[FC Flag]="Current FC", 'Data'[Measure Code] = varTurnover)
                    +
                    Calculate(SUM('Data'[Value]), 'Data'[FC flag]="Actuals" || 'Data'[FC Flag]="Current FC", 'Data'[Measure Code] = varDispTO)), BLANK()),

And the calculated column for current month looks like this 

CM = 
SWITCH(
    TRUE,
    [CMValue] - 11 = 'Calendar'[Month Number] && 'Calendar'[Year] = YEAR(TODAY()),"CM",
    [CMValue] - 11 = 'Calendar'[Month Number] && 'Calendar'[Year] = YEAR(TODAY())-1,"CM",    
    [CMValue] = 'Calendar'[Month Number] &&  'Calendar'[Year] <> YEAR(TODAY()),"CM",
    "-"
)

Hopefully it's clear enough, thanks in advance. 

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Try to refer to my formulas.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous ,

Is it suposse to have CM on both January and December values? What is the purpose of this additional flag?

Maybe you need to change the way you are calculating the CM.

[Anonymous]

Hello, yes, I need it to be on december 2020 and on January 2021 but to have a specific measure work that uses
DateADD(Calendar[Date], -1, year) 

I need to have CM on January 2020 as well so the measure works for january 2021. But doing that breaks CM on 2020 since it got 2 CM in 2020 now and dosen't know which one to pick which breaks any visual that look at 2020. So somehow I need it to ignore the CM on January 2020 and when 2020 is selected it should only filter on December 2020. 

So the purpose of the flag is to look at the current month and for us on the reports that has 2020 current month would be December and on the reports that 2021 it should be January, it's used for our period table. 

If that clears it up a bit. 

Thank you for your help!

[MFelix]

Hi @Anonymous ,

Believe that this is not the best option for you to achieve what you want to calculate, in my opinion there is no need for the flag of the CM you just need to filter the information on the metric you have and then with a DATEADD for example get the previous year data.

So the only thing you need to add in your measure would be the current month/year date.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.