Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Beau A]

I need help with the below measure that will be used on a column graph with Month-Year as the XAxis (Ex. Jan-2021,Feb,2021,March,2021)

1) var Maxdate1 is trying to determine the max date from my date table of any given period (Month for my visual – and my visual will only ever be at the month)
2) HistoricalCount is trying to determine the total count all time
3) Return is trying to display the count of all the historical data (even outside the chart month) that meets the following criteria.

For some reason I am doing something wrong.. can anyone help?

Measure 12 =

var Maxdate1 = max(dim_date[DateKey])

var HistoricalCount = CALCULATE(count(table[subscription_id]),

filter(table,table[status] in {“Active”,“Paused”,“Closed”}),all(dim_date))

return calculate(HistoricalCount,filter(table,table[date_key] <= Maxdate1),

filter(table,table[cancel_date] >= Maxdate1) || table[cancel_date] = Blank()))

?

——————————
Beau A
Analyst
MI
——————————

[Jean-Luc DJEKE]

Hello ,
Can you post your .pbix in order to see how is  done your tabular model please ??

——————————
Jean-Luc DJEKE
Ingénieur BI
——————————
——————————————-

[Vilmar Santos]

Tente esta abaixo. Se não der certo, envia seu pbi por favor

Medida =
VAR Var1 =
    MAX ( dim_date[DateKey] )
    
VAR Var2 =
    CALCULATE (
        COUNT ( table[subscription_id] ),
        FILTER (
            table,
            table[date_key] <= Var1 &&
            table[cancel_date] >= Var1 &&
            table[status] IN { "Active", "Paused", "Closed" } ||
            table[cancel_date] = BLANK ()
        ),
        ALL ( dim_date )
    )
RETURN
    Var2

——————————
Vilmar Santos
——————————
——————————————-