Cumulative Measure from start of the year up until selected month

[BI_Samurai]

Hello,

I would like to create a dynamic calculation for 3 different time logic from start of the year up until the time period selected in the slicer.

The time intelligence is created with a switch measure in order to display dynamically:

• Sales Single month
• Sales Cumulative
• Sales 12 months rolling

Measure Sales Time Logic on Page Test 1 returns correct values for 3 time logics, however, the goal is to show the data only until the time period selected in the year/month slicers meaning that if I select April I should see 4 bars with Jan, Feb, Mar, Apr (not one for April only nor 12 months for the whole year).

Test 1

Test 2 was created with the second calendar (Calendar 2) and Measure Sales Start of the Year Test where I get the correct time period upon the selection from the slicer, however I always get values for single month instead of dynamic time logics for cumulative and 12 months rolling. So this measure work correctly only in the scenario of single month.

Test 2

The goal is to have both correct time logic calculation and display the time period up until the month selected in the slicer.

Does anyone have an idea how to fix this?

Thank you.

My measures are here:

Sales Time Logic =

SWITCH(MIN('SWITCH TIME'[SWITCH]),

    "single month", [Sales Base],

    "cumulative", [Sales Cumulative],

    "rolling", [Sales Rolling])

Sales Start of the Year Test =

CALCULATE([Sales Time Logic],

DATESINPERIOD('📆Calendar'[Date],

STARTOFYEAR('📆Calendar'[Date]),

MAX('📆Calendar'[Month #]), MONTH))

Sales Cumulative =

CALCULATE(

    [Sales Base],

     DATESYTD('📆Calendar'[Date]))

Sales Rolling =

var var_max_date = MAX('📆Calendar'[Date])

var var_min_date = EDATE(var_max_date,-12)

var var_result =

CALCULATE([Sales Base],

 FILTER(ALL('📆Calendar'),

'📆Calendar'[Date]<= var_max_date &&

'📆Calendar'[Date]> var_min_date))

return

var_result

Sales Base =

 SUM(Sales[Total Sales])

[BI_Samurai]

Hello @v-yangliu-msft 

thank you so much for your reply. I did a test on cumulative measure. Unfortunately when I selected April in month slicer I still see only April instead Jan-Apr time period. The flag = 1 from cumulative measure is returned only for April as well. I assume it should show in the period of Jan-Apr.  Any idea what I am doing wrong here? thanks a lot for your support.

cumulative =

var _selectyear=SELECTEDVALUE('📆Calendar'[Year])

var _selectmonth=SELECTEDVALUE('📆Calendar'[Month])

var _selectmonthnumber=MAXX(FILTER(ALL('📆Calendar'),'📆Calendar'[Year]=_selectyear&&'📆Calendar'[Month]=_selectmonth),'📆Calendar'[Month #])

var _column=

SELECTCOLUMNS(

    FILTER(ALL('📆Calendar'),

    '📆Calendar'[Year]=_selectyear&&'📆Calendar'[Month #]<=_selectmonthnumber),"test",'📆Calendar'[Month #])

return

IF(

    MAX(Sales[Year])=_selectyear&&MONTH(MAX(Sales[Date])) in _column,1,0)

Flag =

SWITCH(

    TRUE(),

    MAX('SWITCH TIME'[SWITCH]) = "Cumulative",[cumulative])

[v-yangliu-msft]

Hi  @BI_Samurai ,

Here are the steps you can follow：

1. Create calculated table.

Date =
var _table=
CALENDAR(  
    DATE(2023,1,1),
    DATE(2024,5,1))
return
ADDCOLUMNS(
    _table,
    "Year",YEAR([Date]),
    "Month_Number",MONTH([Date]),
    "Month_Text",FORMAT([Date],"mmm"))

2. Enter data – create a table.

3. Create measure.

Single month =
var _selectyear=SELECTEDVALUE('Date'[Year])
var _selectmonth=SELECTEDVALUE('Date'[Month_Text])
return
IF(
    MAX('Table'[Year]) = _selectyear&&MAX('Table'[Month]) =_selectmonth,1,0)

cumulative =
var _selectyear=SELECTEDVALUE('Date'[Year])
var _selectmonth=SELECTEDVALUE('Date'[Month_Text])
var _selectmonthnumber=MAXX(FILTER(ALL('Date'),'Date'[Year]=_selectyear&&'Date'[Month_Text]=_selectmonth),'Date'[Month_Number])
var _column=
SELECTCOLUMNS(
    FILTER(ALL('Date'),
    'Date'[Year]=_selectyear&&'Date'[Month_Number]<=_selectmonthnumber),"test",'Date'[Month_Number])
return
IF(
    MAX('Table'[Year])=_selectyear&&MONTH(MAX('Table'[Date])) in _column,1,0)

12 months rolling =
var _selectyear=SELECTEDVALUE('Date'[Year])
var _selectmonth=SELECTEDVALUE('Date'[Month_Text])
var _date=MAXX(FILTER(ALL('Date'),'Date'[Year]=_selectyear&&'Date'[Month_Text]=_selectmonth),[Date])
var _mindate=EOMONTH(_date,-13)
var _maxdate=EOMONTH(_date,-1)
return
IF(
    MAX('Table'[Date]) > _mindate && MAX('Table'[Date]) <= _maxdate,1,0)

Flag =
SWITCH(
    TRUE(),
    MAX('Slicer_Table'[Slicer]) = "Single month",[Single month],
    MAX('Slicer_Table'[Slicer]) = "Cumulative",[cumulative],
    MAX('Slicer_Table'[Slicer]) = "12 months rolling",[12 months rolling])

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

5. Result:

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