# Power BI Exchange

View Only

## DAX Formula Help

• #### 1.  DAX Formula Help

Bronze Contributor
Posted May 05, 2022 01:58 PM
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 May 09, 2022 06:49 AM
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 May 14, 2022 03:16 PM
Edited by Vilmar Santos May 14, 2022 03:18 PM

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

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