Values till the selected month

[Anonymous]

Hi Community,

I need one small help. In my grid there is month slicer. If in the month slicer march month is selected then the grid must display values from jan to march. as of now it displays only the march value. I must get the values in the higlighted part. Your help would

be highly appreciated.

DAX:

value_acvsbuDummy3 =

var period = SELECTEDVALUE('3a CWC - CWC as % of R12M Revenue'[Period])

var monthz = CALCULATE(MAX('3a CWC - CWC as % of R12M Revenue'[Month1]), MONTH('3a CWC - CWC as % of R12M Revenue'[Period])=period)

return

IF(monthz<period,CALCULATE(SUM(LE[AC])))

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following steps to meet your requirement.

1. Create a calendar table,

3a CWC - CWC as % of R12M Revenue =
GROUPBY (
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Month", FORMAT ( [Date], "MMM" ),
        "MonthNumber", MONTH ( [Date] )
    ),
    [Month],
    [MonthNumber]
)

1. Then we can create a measure, when keep no relationship between two tables, only this measure will be affected by the slicer.

Measure =
VAR date_ =
    MIN ( '3a CWC - CWC as % of R12M Revenue'[Month number] )
RETURN
    CALCULATE ( SUM ( 'LE'[AC] ), FILTER ( 'LE', 'LE'[Month number] <= date_ ) )

And we can get the result like this,

BTW, pbix as attached.

Best regards,

[AiolosZhao]

Hi @Anonymous ,

I have a workaround hope you can like it,

1. Create an index for your month column from 1.

2. Then you will get 1-12 for your month column, and put the index as the filter.

3. Then choose the Less than or equal to in the filter type like the below image

Aiolos Zhao

[Anonymous]

Yes it is working but it is affecting all the rows. Still i will give it try with my team members. If they accept ill accept this as solution. Thanks mate

[AiolosZhao · Accepted solution]

Hi @Anonymous ,

Glad to help you~

Could you please accept my answer or @v-lid-msft 's answer as a solution?

So other people will know that the problem is solved.

Thanks.

Aiolos Zhao

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following steps to meet your requirement.

1. Create a calendar table,

3a CWC - CWC as % of R12M Revenue =
GROUPBY (
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Month", FORMAT ( [Date], "MMM" ),
        "MonthNumber", MONTH ( [Date] )
    ),
    [Month],
    [MonthNumber]
)

1. Then we can create a measure, when keep no relationship between two tables, only this measure will be affected by the slicer.

Measure =
VAR date_ =
    MIN ( '3a CWC - CWC as % of R12M Revenue'[Month number] )
RETURN
    CALCULATE ( SUM ( 'LE'[AC] ), FILTER ( 'LE', 'LE'[Month number] <= date_ ) )

And we can get the result like this,

BTW, pbix as attached.

Best regards,