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 ,

 

To create a measure as below.

 

Measure = 
VAR maxmonth =
    MAXX ( ALL ( Table1 ), Table1[Month] )
VAR maxmonth0 =
    CALCULATE (
        MAX ( Table1[Month] ),
        FILTER ( ALL ( Table1 ), Table1[Cost] = 0 ),
        VALUES ( Table1[Supplier] )
    )
VAR a =
    CALCULATE ( SUM ( Table1[Cost] ), FILTER ( Table1, Table1[Month] = maxmonth ) )
RETURN
    IF (
        a = 0,
        0,
        CALCULATE (
            COUNT ( Table1[Cost] ),
            FILTER ( Table1, Table1[Month] > maxmonth0 && Table1[Month] <= maxmonth )
        )
    )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, I'll check this out tomorrow. Thanks for the help! 

  • 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
      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.