Flag (measure) to work for visuals without Date context

[Foxxon28]

Dear reader,

I have a dashboard that needs to dynamically show cumulative [Sales] based on selected [Channel]. With the help of some of you I have created the following Flag measure that works:

Max Month Flag =

var _Channel=

SWITCH(

    TRUE(),

    ISFILTERED('dim channel'[Channel]) && OR({"A"} in VALUES('dim channel'[Channel]), {"B"} in VALUES('dim channel'[Channel])), 1,

    NOT(ISFILTERED('dim channel'[Channel])), 1,

    0

)

var _month_A= MAXX(FILTER(ALL('Fact'),[Channel]="A"),[Date])

RETURN

SWITCH(

    TRUE(),

    _Channel= 1 && MAX(Fact[Date])<= _month_A,1,

    _Channel=0,1,0

)

Basically this Flag checks if "A" is filtered, and if so, returns a 1 for every Date it has values for.

This Flag is put on a visual with Max Month Flag = 1.

This works great for visuals that have any sort of date logic in it. However, I also have some table and matrix visuals that do not include any [Date] logic and have their own YTD / YOY calculations.

I need to find a way to make the flag work for those visuals aswell, as they only seem to work for those that I add [Date] to.

Any suggestions?

Can I incorporate the Flag into a measure for [Sales] perhaps? I've failed to do so, but maybe you know a way. Or any other method!

Kind regards,

Daniël

[Anonymous]

Hi @Foxxon28

You should write your code according to the logic you need.

If you want to calculate sales based on Max Month Flag, you can consider the following code:

Conditional Sales = 
VAR _MaxMonthFlag = 
    [Max Month Flag]  // Assuming this is your existing Flag measure
RETURN
IF(
    _MaxMonthFlag = 1,
    CALCULATE(
        [Total Sales],  // Replace this with your actual total sales measure
        ALL('Fact')  // This removes any filters on the 'Fact' table, consider adjusting as necessary
    ),
    BLANK()  // Returns blank if the conditions are not met
)

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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

[Foxxon28]

Thanks for the reply @Anonymous ,

However this does not do what I want. I will provide you with some dummy data as requested: 

Channel	YearMonth	Sales
Shop	2024-01	10
Shop	2024-02	11
Phone	2024-02	5
Phone	2024-01	10
Phone	2024-02	20
Webshop	2023-12	15
Webshop	2024-01	30

I have a [Channe] Slicer on the page, that needs to dynamically filter the page.

The challenge is that all [Channel] data is from a different source, and therefore has different MAX [YearMonth]. That way a lot of the times "Phone" and "Shop" have up-to-date information, whereas "Webshop" is 1 month behind.

The beforementioned FLAG makes it so that if "Webshop" is filtered, it checks if "Webshop MAX Yearmonth" =/= MAX Yearmonth overall. If thats not that case it'll automatically flag the new month and remove it from the data.

However, this doesn't work for visuals that do not include any [Date] related fields. Therefore I want to find a new way to always make this logic work, for any type of visual. (Some extra background is a previous post of mine: Re: Cumulative Sales based on MIN month of a MAX m... - Microsoft Fabric Community) 

For this instance an example would be:

IF selectedvalue [Channel] contains "Webshop", use the MAX month of [Channel] = "Webshop"

IF selectedvalue [Channel] contains <> "Webshop" use the MAX month of [YearMonth]

Default (Nothing filtered): Use MAX month of [Channel] = "Webshop"

For most of the report we use cumulative [Sales] measures.

If you need any more input, please let me know,

Thanks! 🙂

Daniël

[Anonymous · Accepted solution]

Hi @Foxxon28 

For your question, here is the method I provided:

“Table”

Create a measure.

Max Date = 
var _selectChannel = SELECTEDVALUE('Table'[Channel])
var _WebshopMaxData = 
CALCULATE(
    MAX('Table'[YearMonth]), 
    FILTER(
        ALL('Table'), 
        'Table'[Channel] = "Webshop"
    )
)
var _MaxData = 
CALCULATE(
    MAX('Table'[YearMonth]), 
    FILTER(
        ALL('Table'), 
        'Table'[Channel] <> "Webshop"
    )
)
RETURN 
IF(
    _selectChannel = "Webshop" || ISBLANK(_selectChannel), 
    _WebshopMaxData, 
    _MaxData
)

For the calculation [Sales] you need to create a virtual table.

virtual table = 
SELECTCOLUMNS(
    'Table', 
    "channel", 'Table'[Channel], 
    "yearmonth", 'Table'[YearMonth], 
    "sales", 'Table'[Sales]
)

Create a measure.

Total Sales = 
CALCULATE(
    SUM('virtual table'[sales]), 
    FILTER(
        ALL('virtual table'),
        'virtual table'[channel] = MAX('virtual table'[channel]) 
        &&  
        'virtual table'[yearmonth] = 'Table'[Max Date]
    )
)

Here is the result.

 

Regards,

Nono Chen

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