Forum Discussion
DATEDIFF Function with certain provisions
Hello,
I have 3 columns on table 1 , "Value" , "Type", and "Date". Type includes projections for Sales and Expenses. The Dates column ranges from Sep 2016-Dec 2018 for Sales and from Sep 2017-Dec 2019 for Expenses. I am currently trying to find the date diff from the minimum date for seperatly for sales and expenses in one column. Is there a function I could use DATEDIFF with in conjuction with something like "WHERE [Value]= Sales OR Expenses"? In result, I should get a new column where Sep 2016 for Sales=0 as well as Sep 2017 for Expenses=0. Please let me know if anyone needs a little clarification. Thank you so much for your time.
Hi 1241pm,
Please check if the following DAX return your expected result, if not, kindly share your sample to me and post desired result based on the sample.
Column 2 = IF(Table1[Type]= "Sales",1,0)
date1 = CALCULATE(MIN(Table1[Date]),FILTER(ALL(Table1),Table1[Column 2]=1))
date2 = CALCULATE(MIN(Table1[Date]),FILTER(ALL(Table1),Table1[Column 2]=0))
Result = IF(Table1[Column 2]=1,DATEDIFF(Table1[date1],Table1[Date],DAY),DATEDIFF(Table1[date2],Table1[date],DAY))
For more details, please check the pbix as attached.
Regards,
Frank
1 Reply
Hi 1241pm,
Please check if the following DAX return your expected result, if not, kindly share your sample to me and post desired result based on the sample.
Column 2 = IF(Table1[Type]= "Sales",1,0)
date1 = CALCULATE(MIN(Table1[Date]),FILTER(ALL(Table1),Table1[Column 2]=1))
date2 = CALCULATE(MIN(Table1[Date]),FILTER(ALL(Table1),Table1[Column 2]=0))
Result = IF(Table1[Column 2]=1,DATEDIFF(Table1[date1],Table1[Date],DAY),DATEDIFF(Table1[date2],Table1[date],DAY))
For more details, please check the pbix as attached.
Regards,
Frank