Cumulative total based on the selected month

[Kolumam]

I have a date table and a Meter table. There exists a one to many relationship between them with a cross filter on both sides. I am trying to calculate a cumulative total of a column in the meter table. The cumulative total works fine but when I select a particular month, it shows the split-up of values only for that month. Rather than that, it needs to show the values from the beginning till the selected month. See the below formula.

Cumulative Size Operating - planned (modified) = 
CALCULATE (
    SUM ( Meter[Size (MWp)] ),
    FILTER (
        ALLEXCEPT(Meter,Meter[Name],Meter[Size (MWp)]),
        AND (
            Meter[Energized_Date__c] <= MAX(Dates[Last day of month]),
            AND (
                Meter[Type_of_Meter__c] = "Energy Generation",
                
                Meter[Status__c]<> "Decomissioned"
                )
            )
        )
    )
   

Wrong Result: Here it shows the size of 2.93 only for that month, whereas I need the total size of 99.76 with a split up of all the values.

[Anonymous]

Hi @Kolumam 

Can you please upload the pbix and data files to OneDrive or GoogleDirve and share the link to check out.

Please also indicate what is the output expected in which visual when a specific month is selected.

Cheers

CheenuSing

[Kolumam]

I can't share the pbix but the below is the output that I want:

Month           Size   Cumulative Size

Jan 2019        5        5

Feb 2019       6        11

March 2019   7        18

Now I have Month as the filter. When I filter Feb 2019 I wanted to get the below.

Month     Cumulative Size

Jan 2019  5

Feb 2019 11

[v-lid-msft · Accepted solution]

Hi @Kolumam ,

First of all, we can create a spreated calculated table as the slicer, 

SlicerTable = DISTINCT('Table'[Month])

Then we can use a measure in visual filter to meet your requirement:

Cumulative Size = CALCULATE(SUM('Table'[Size]), FILTER(ALLSELECTED('Table'),[Month]<=SELECTEDVALUE('Table'[Month])))

Best regards,

[Anthony_G1]

Hello,

I need to do something similar to the original poster, except display the Cumulative Size in a Card visual. Downloading your pbix, if I drag the Cumulative Size measure into a Card visual, it displays as (Blank). Is there a way to do this? Thank you!

[Kolumam]

can you share the pbix? @v-lid-msft 

[v-lid-msft]

Hi @Kolumam ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @Kolumam ,

Please download the sample pbix file at the end of our previous reply.

Best regards,