Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |