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

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