Forum Discussion
Count running accruals
Hi all, I am want to make a measure that counts the current running accruals of a company. Here's how the raw data would look like on the left, and I plan to arrange the data like this on the rig...
Hi Anonymous ,
Is that the excepted result you need?
Measure 3 = SUMX ( FILTER ( ALL ( Table2[Supplier] ), Table2[Supplier] <= MAX ( Table2[Supplier] ) ), [Measure 2] )
hi v-frfei-msft,
I can only send dummy/sample data, found in "Table 2". You can find the PBIX file here.
I added more than one data entry per period and also added cost type "Actual" to resemble my original raw data.
Thank you.
Hi Anonymous ,
LRA =
VAR k =
FILTER ( Table2, Table2[type] = "accrual" )
VAR maxmonth =
MAXX ( ALL ( Table2 ), Table2[Month] )
VAR maxmonth0 =
CALCULATE (
MAX ( Table2[Month] ),
FILTER ( ALL ( Table2 ), Table2[Cost] = 0 ),
VALUES ( Table2[Supplier] ),
KEEPFILTERS ( k )
)
VAR a =
CALCULATE (
SUM ( Table2[Cost] ),
FILTER ( Table2, Table2[Month] = maxmonth ),
KEEPFILTERS ( k )
)
RETURN
IF (
a = 0,
0,
CALCULATE (
DISTINCTCOUNT ( Table2[Month] ),
FILTER ( Table2, Table2[Month] > maxmonth0 && Table2[Month] <= maxmonth ),
KEEPFILTERS ( k )
)
)
hi v-frfei-msft,
The result is almost realized. Apologies, I forgot to mention that the accruals for each company, per period should be displayed as a running total. I think I have to edit this part of the code?
VAR maxmonth0 = CALCULATE ( MAX ( 'Central (FLO)'[AfP] ), FILTER ( ALL ( 'Central (FLO)' ), 'Central (FLO)'[Company code amount] = 0 ), VALUES ( 'Central (FLO)'[Company Name] ), KEEPFILTERS ( k )Thanks for your continuous help.
Hi Anonymous ,
Is that the excepted result you need?
Measure 3 = SUMX ( FILTER ( ALL ( Table2[Supplier] ), Table2[Supplier] <= MAX ( Table2[Supplier] ) ), [Measure 2] )