Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    Is that the excepted result you need?

     

    Measure 3 =
SUMX (
    FILTER (
        ALL ( Table2[Supplier] ),
        Table2[Supplier] <= MAX ( Table2[Supplier] )
    ),
    [Measure 2]
)

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous ,

 

Could you please create a sample pbix to me? Please upload your files to One Drive and share the link here.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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 )
        )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

       

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        7 years ago

        Hi Anonymous ,

         

        Is that the excepted result you need?

         

        Measure 3 =
SUMX (
    FILTER (
        ALL ( Table2[Supplier] ),
        Table2[Supplier] <= MAX ( Table2[Supplier] )
    ),
    [Measure 2]
)