cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Tableau DateTrunc calculation convert to Power BI DAX

COUNTD(if (DATETRUNC('month',[Opened Date]) = DATETRUNC('month',[Max Opened Date])) then [Incident Number] end)

I tried the below calculation but the values are not matching

IF( YEAR(Table[Opened Date]) = YEAR(MAX(Table[Opened Date])) && MONTH(Table[Opened Date]) = MONTH(Max(Table[Opened Date])),Table[Incident Number]),0)

2 ACCEPTED SOLUTIONS
Super User

Hi @Puneeth_123
You didn't succeed with the formula because

 Incident Numbe

is text and 0 is number.
Just modify it to :

test = if (MONTH('Table'[Opened Date])=MONTH('Table'[Max Opened Date]) &&
Year('Table'[Opened Date])=year('Table'[Max Opened Date]),
'Table'[Incident Number],"0")
Regards,
Rita Fainshtein | Microsoft MVP
Super User

The solution I proposed works accurately, including the counting step.

You can download the file I attached in the first response and check.

It is impossible to understand why there are inconsistencies between your 2 reports because there is no access to the model.

You can prepare a sample file that is in the same structure as your original model and upload it here or as a new question.

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

Regards,
Rita Fainshtein | Microsoft MVP
5 REPLIES 5
Super User

Hi @Puneeth_123
Can you please show an example of the data and the desired result?

Regards,
Rita Fainshtein | Microsoft MVP
Regular Visitor

Below is the sample Data, Incident number and Opened date are direct columns and Max opened date is custome column created using "Max" dax function. I am trying to create custome column using Dax "IF( YEAR(Table[Opened Date]) = YEAR(MAX(Table[Opened Date])) && MONTH(Table[Opened Date]) = MONTH(Max(Table[Opened Date])),Table[Incident Number]),0)"  then simple doing distinct count of that incident number. Notes- No continue dates.

 Incident Number Opened Date Max Opened Date INC12345 7/16/23 12:00 AM 10/14/23 12:35 AM INC12367 7/20/23 3:01 PM 10/14/23 12:35 AM INC12348 7/26/23 6:09 AM 10/14/23 12:35 AM INC12876 9/20/23 2:35 PM 10/14/23 12:35 AM INC12348 9/14/23 4:35 AM 10/14/23 12:35 AM INC12340 9/16/23 1:35 AM 10/14/23 12:35 AM INC12349 10/8/23 12:35 AM 10/14/23 12:35 AM INC12346 10/12/23 2:45 PM 10/14/23 12:35 AM INC12987 10/14/23 3:35 AM 10/14/23 12:35 AM
Super User

Hi @Puneeth_123
You didn't succeed with the formula because

 Incident Numbe

is text and 0 is number.
Just modify it to :

test = if (MONTH('Table'[Opened Date])=MONTH('Table'[Max Opened Date]) &&
Year('Table'[Opened Date])=year('Table'[Max Opened Date]),
'Table'[Incident Number],"0")
Regards,
Rita Fainshtein | Microsoft MVP
Regular Visitor

Tried keeping "0" and null also but I am getting more than double count EX: in Tableau count 5,949 in power BI 11,898

Super User

The solution I proposed works accurately, including the counting step.

You can download the file I attached in the first response and check.

It is impossible to understand why there are inconsistencies between your 2 reports because there is no access to the model.

You can prepare a sample file that is in the same structure as your original model and upload it here or as a new question.

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

Regards,
Rita Fainshtein | Microsoft MVP