# Power BI Exchange

View Only

## DAX Formula Help

• #### 1.  DAX Formula Help

Bronze Contributor
Posted 18 days ago
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
------------------------------

• #### 2.  RE: DAX Formula Help

Bronze Contributor
Posted 14 days ago
Hello @Beau A,
Can you post your .pbix in order to see how is  done your tabular model please ?​

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

• #### 3.  RE: DAX Formula Help

Posted 9 days ago
Edited by Vilmar Santos 9 days ago

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
------------------------------
-------------------------------------------
Original Message:
Sent: May 05, 2022 01:57 PM
From: Beau A
Subject: DAX Formula Help

Medida = VAR Var1 = MAX ( dim_date[DateKey] ) VAR VAR2 = CALCULATE ( CONTAGEM (tabela[subscription_id] ), FILTRO (tabela, mesa[date_key] <= Var1 && tabela[cancel_date] >= Var1 && tabela[status] IN { "Ativo", "Pausado", "Fechado" } || tabela[cancel_date] = BLANK () ), ALL (dim_date ) RETORNO Var2x por favor

------------------------------
Vilmar Santos
------------------------------
-------------------------------------------
original Mensagem:
Enviado: 05 de maio de 2022 01:57 PM
De: Beau Um
Assunto: DAX Fórmula Ajuda

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

------------------------------