rolling up data based on date to get a cumulative viewpoint

[SachinC]

Hi,

i want to create a dashboard to report on elements from multiple tabs in an excel workbook.

one of the tabs has date , week number, month number and year. this will be used for my slicers.

other tabs have random dates for certain events, i.e. sales, developent etc... with retrospective values ie amount, dev days etc...

by selecting a filter, eg week 4, month 3, year 2017, i want a rollup graph of all activity to that date, i.e. all sales to the date filter selected, all dev days to date filter selected etc... how do i get this done in powerbi pls? thank you. 

[v-sihou-msft]

@SachinC

According to your description, you want to calculate the cumulative total sales and total dev days on day level. Right?

You just need to apply the date filter up to current row in your calculate() function. If you have one table for each event type, you can create measures like below:

Cumulative Sales :=
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Total Dev Days :=
CALCULATE (
    COUNTROWS ( 'Dev' ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

If you only have one event table with a column tagging different event type, you measures can be like below:

Cumulative Sales :=
CALCULATE (
    COUNTROWS ( 'Event' ),
    FILTER ( ALL ( 'Event'[Type] ), 'Event'[Type] = "Sales" ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Total Dev Days :=
CALCULATE (
    COUNTROWS ( 'Event' ),
    FILTER ( ALL ( 'Event'[Type] ), 'Event'[Type] = "Developer" ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Regards,

[Phil_Seamark]

Is this what you are after?

http://www.daxpatterns.com/cumulative-total/