Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
New to DAX and struggling to find a calculation to perform the following:
Data is like this:
FiscalDate CurrentInventoryAmt SalesAmt RemainderAmt 20180714 1000 100 900 20180707 1000 200 700 20180701 1000 1000 -300 20180624 1000 0 -300
Couple of notes:
1. The Fiscal date needs to look backwards (Descending order), fiscal dates are the week start dates.
2. RemainderAmt is a running total calculated as taking the remainderAmt of the last week and subtracting SalesAmt.
I need to find:
For each fiscal date, i need to find the number of days where the RemainderAmt went negative
For e.g.
For 20180714, RemainderAmt went negative in 20180701, so the measure needs to return 14 (days between 20180714 and 20180701)
For 20180707, the measure will return 7 days
For 20180701, the measure will return 0 days since the remainder is already negative.
Any guidance will be appreciated!
Thanks
@bi_quest18 Please try add to add below three fields as "New Column"
MaxDate (Negative amount)
MaxDate = VAR _MaxDate = CALCULATE(MAX(Test37[FiscalDate]),FILTER(Test37,SIGN(Test37[RemainingAmt])=-1)) VAR _MaxDateFinal = DATE(LEFT(_MaxDate,4),MID(_MaxDate,5,2),RIGHT(_MaxDate,2)) RETURN _MaxDateFinal
FiscalDate conversion to proper Date datatype
FiscalDateNew = DATE(LEFT(Test37[FiscalDate],4),MID(Test37[FiscalDate],5,2),RIGHT(Test37[FiscalDate],2))
Then, here is the Days logic
Days = IF(Test37[MaxDate]>Test37[FiscalDateNew],0,DATEDIFF(Test37[MaxDate],Test37[FiscalDateNew],DAY))
Proud to be a PBI Community Champion
Hi Pattermmanohar, This is great. thanks!
Since the data set is huge = 2 billion+ rows, the current cube size is 127GB.
Is it possible to do this in measures? I am trying to minimize calculated columns to conserve the footprint of the deployed cube.
Thanks again
User | Count |
---|---|
106 | |
86 | |
81 | |
72 | |
71 |
User | Count |
---|---|
112 | |
100 | |
98 | |
72 | |
66 |