Cumulative average on power bi

Question

I have a table which contains a list of products scores by date:

From this table, I have to make a plot of the cumulative percentage of each quality by date.

At this moment I have the percentage of each class by day:

For that I used this measurement:

Measure =
CALCULATE (
    SUM ( Table1[Percentage_By_Class] ),
    FILTER ( Table1, Table1[Date] = MAX ( Table1[Date] ) ),
    ALLEXCEPT ( Table1, Table1[Score] )
)
    / CALCULATE (
        SUM ( Table1[Percentage_By_Class] ),
        FILTER ( ALL ( Table1 ), Table1[Date] = MAX ( Table1[Date] ) )
    )

But this only considers the percentage of each day. I need to consider all previous dates. E.G. for day 2 I need to consider days 1 and 2, for day 3 I need to consider days 1,2,3 and so on.

How can I accomplish this?

SUNAY T. SUNAY T. · Accepted Answer · 2019-02-23T09:42:39

in my opinion, you need a calendar for the date first, you can create a table easily bay dax function =CALENDARAUTO() And mark it as a calendar table,

After that, you can use a DATEMTD or a DATEYTD function for your coding purpose.

here are the steps:

1 - https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

select left pane --> table --> modelling / create table and add dax formula

2- reference the table as a date table, right click on the table from the right pane

after then you can use data functions like YTD MTD ,

new measure :

1st mesure : AVG1 = AVG(DATA_) 2nd measure : YTD AVG ALL = CALCULATE([AVG1];DATESYTD(CALENDAR[DATE]))

REF: https://docs.microsoft.com/en-us/dax/dateadd-function-dax

then you can use MONTH(CALENDAR(DATE)) on left and YTD AVG as a value at any table...

regards.

SUNAY

Cumulative average on power bi

1 Answers