calculate cumulative value

[Anonymous]

Hi guys, I want to calculate a cumulative value with selected date range, and the slicer may switch  M to W(week) or D(day),

is there to be a good way to fullfill that? Thank you

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Use Enter data to create a table.

2. Create a calendar table.

Date =
CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

3. Create measure.

Flag_day =
var _mindate=MINX(ALLSELECTED('Table'),'Table'[Date])
var _maxdate=MAXX(ALLSELECTED('Table'),'Table'[Date])
return
IF(
MAX('Date'[Date])>=_mindate&&MAX('Date'[Date])<=_maxdate,1,0)

Flag_week =
var _mindate=MINX(ALLSELECTED('Table'),'Table'[Date])
var _maxdate=MAXX(ALLSELECTED('Table'),'Table'[Date])
return
IF(
   MAX('Date'[Week])>=WEEKNUM(_mindate,1)&&MAX('Date'[Week])<=WEEKNUM(_maxdate,1),1,0)

Flag_month =
var _mindate=MINX(ALLSELECTED('Table'),'Table'[Date])
var _maxdate=MAXX(ALLSELECTED('Table'),'Table'[Date])
return
IF(
MAX('Date'[Month])>=MONTH(_mindate)&&
MAX('Date'[Month])<=MONTH(_maxdate),1,0)

Flag =
var _select=SELECTEDVALUE('Table2'[Select])
return
SWITCH(
    TRUE(),
    _select="Month",[Flag_month],
    _select="Week",[Flag_week],
    _select="Day",[Flag_day])

amount_measure =
SUMX(FILTER(ALL('Table'),'Table'[Date]=MAX('Date'[Date])),[Amount])

calculate cumulative value =
SUMX(FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])),[amount_measure])

4. Place [Flag]in Filters, set is=1, apply filter.

5. Result:

Select "Month", "Week", "Day" according to the slicer, and display the corresponding measure

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

sorry, I found no ways to send you a file or link😐

[Ashish_Mathur]

Google Drive/One Drive and a host of other service providers.

[Anonymous]

edited

[Ashish_Mathur]

That link takes me to an error page.