Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Puneeth_123
Regular Visitor

Tableau DateTrunc calculation convert to Power BI DAX

Please help me to convert below Tableau Datetrunc calculation 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

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")
Ritaf1983_2-1697337941175.png
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

Hi @Puneeth_123 

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.

Ritaf1983_0-1697349871280.png

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
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

5 REPLIES 5
Ritaf1983
Super User
Super User

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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 NumberOpened DateMax Opened Date
INC123457/16/23 12:00 AM10/14/23 12:35 AM
INC123677/20/23 3:01 PM10/14/23 12:35 AM
INC123487/26/23 6:09 AM10/14/23 12:35 AM
INC128769/20/23 2:35 PM10/14/23 12:35 AM
INC123489/14/23 4:35 AM10/14/23 12:35 AM
INC123409/16/23 1:35 AM10/14/23 12:35 AM
INC1234910/8/23 12:35 AM10/14/23 12:35 AM
INC1234610/12/23 2:45 PM10/14/23 12:35 AM
INC1298710/14/23 3:35 AM10/14/23 12:35 AM

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")
Ritaf1983_2-1697337941175.png
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

Hi @Puneeth_123 

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.

Ritaf1983_0-1697349871280.png

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
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.