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

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

hi v-frfei-msft ,

 

What if I have multiple entries of cost for one period/month, what would I add into the measure? My actual raw data is like this: 

 

The cost type is categorized into two types: Accrual and Actual. I've added a filter to consider only the Accrual cost and replaced the columns appropriate to my data, and when I inserted the measure, the result looked like this:

 

 

Thanks for the help

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity 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
      Icon for Community Support rankCommunity 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.