Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
In the attached how would I not display the sum value 837418.20 for the rest of the days where there are no actual values yet? Please see THE FORMULA BELOW and you would understand what I am trying to do.
Solved! Go to Solution.
Hi @venunm,
>>Hi do you know how you got the 'current' in you formula? and what is dates?
"Current" is the " New Column.Disbursed Advance Amount" column, "Data" field has auto analyzed to date hierarchy.
Regards,
Xiaoxin Sheng
hi, Transform your dax to this:
Measure=IF (Column <>Blank(),Calculation, Blank())
In your case Column is NewColumn.Disbursed Advance Amount
And Measure is Total PP This Month
Hi thanks for getting back to me on this. What is 'calculation' in the If statement you used below?
Also my NewColumn.Disbursed Advance Amount is not populating when I start the formula?
try this:
Total PP This Month=If(Calculate(Sum(Merge1[NewColumn.Disbursed Advance Amount])<>Blank),Calculate(Sum(Merge1[NewColumn.Disbursed Advance Amount]),Filter(ALL-------------); Blank()
Hey still not getting thru...I might be doing something really silly....Please see below
Column = IF(CALCULATE(sum(Merge1[NewColumn.Disbursed Advance Amount])<>BLANK),CALCULATE(sum(Merge1[NewColumn.Disbursed Advance Amount]),FILTER(ALL(Merge1),Merge1[Days into Quarter]<MAX(Merge1[Days into Quarter]))); BLANK()
Try changing this to simplify the dax formula:
Mymeasure = VAR SumDAA = CALCULATE ( SUM ( Merge1[NewColumn.Disbursed Advance Amount] ) ) RETURN IF ( [SumDAA] <> BLANK (), CALCULATE ( [SumDAA], FILTER ( ALL ( Merge1 ), Merge1[Days into Quarter] < MAX ( Merge1[Days into Quarter] ) ) ), BLANK () )
it says the value for 'SUMDAA' cannot be determined. Either 'SumDAA' doesnt exist, or there is no current row for a column named 'SumDAA'?
I have tried using both new measure and new column.
Hi @venunm,
Can you please share us a sample file to test?
Regards,
Xiaoxin Sheng
yeah sorry, delete [] from SumDAA
Hi, so as you can see from the above, the 'My measure' formula is doing the job of not repeating the max value till 31st January but adding up incorrectly. It should be cumulative i.e. Jan 4th should start from 41,000, Jan 5th should be newcolumn.disbursed advance amount 168,139.69 + 41,000 = 209,139.69 and so on. The values should be same as the 'Total PP this month' column. Is it something to do with the formula?
Formula used is:
My Measure = VAR SumDAA =
CALCULATE ( SUM ( Merge1[NewColumn.Disbursed Advance Amount] ) )
RETURN
IF (
SumDAA <> BLANK (),
CALCULATE (
SumDAA,
FILTER (
ALL ( Merge1 ),
Merge1[Days into Quarter] < MAX ( Merge1[Days into Quarter] )
)
),
BLANK ()
)
Hi @venunm,
Based on my understanding, you want to hide the blank row of the "NewColumn.Disbursed Advance Amount" column , right?
If this is a case, you can refer to below formula if it works on your side:
Measure:
RollingTotal = CALCULATE(SUMX(FILTER(ALL(Sheet1),[Date]<=MAX([Date])),[Current]),FILTER(Sheet1,Sheet1[Current]<>BLANK()))
Result:
Drag measure and current.
Add target field.
BTW, the best simple way to hide the blank records is use the visual level filter.(all function cannot cancel this filter affect)
Regards,
Xiaoxin Sheng
Hi do you know how you got the 'current' in you formula? and what is dates?
Hi @venunm,
>>Hi do you know how you got the 'current' in you formula? and what is dates?
"Current" is the " New Column.Disbursed Advance Amount" column, "Data" field has auto analyzed to date hierarchy.
Regards,
Xiaoxin Sheng
Hi also, I have the data in the table displayed by day as above screenshot. How do I add a measure with rolling weekly amounts displayed by week so I can graph the data to show rolling progress by week?
EXCELLENT! THIS WORKS. Thank you for all the help.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |