dynamic "timeperiod" report filter.

[pbapuji]

How to achive the above senario, please help me any one on this.

Thanks in Advance.

[v-yuezhe-msft · Accepted solution]

@pbapuji,

Create the checkmonth column using DAX below.

checkmonth = IF(MONTH(TODAY())=1,IF(TODAY()<DATE(YEAR(TODAY()),MONTH(TODAY()),10),11,12),IF(MONTH(TODAY())=2,IF(TODAY()<DATE(YEAR(TODAY()),MONTH(TODAY()),10),12,MONTH(TODAY())-1),IF(TODAY()<DATE(YEAR(TODAY()),MONTH(TODAY()),10),MONTH(TODAY())-2,MONTH(TODAY())-1)))

And create the measure using Dax below.

Measure 3 = IF(MONTH(TODAY())=1||MONTH(TODAY())=2,CALCULATE(SUM(Table[Value]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())-1 &&Table[checkmonth]=MONTH(Table[Date]))),CALCULATE(SUM(Table[Value]),FILTER(Table,Table[checkmonth]=MONTH(Table[Date]))))

Regards,
Lydia

[v-yuezhe-msft]

@pbapuji，

Create a column using DAX below in your table.

checkmonth = IF(TODAY()<DATE(YEAR(TODAY()),MONTH(TODAY()),10),MONTH(TODAY())-2,MONTH(TODAY())-1)

Then create a measure using the following formula.

Measure = CALCULATE(SUM(Table[Value]),FILTER(Table,Table[checkmonth]=MONTH(Table[Date])))

You can review my example.


Regards,
Lydia

[pbapuji]

Thanks for your information,
but it's not working for if the month is January and February.
please help me on this..
please let me know you need more information.

[v-yuezhe-msft · Accepted solution]

@pbapuji,

Create the checkmonth column using DAX below.

checkmonth = IF(MONTH(TODAY())=1,IF(TODAY()<DATE(YEAR(TODAY()),MONTH(TODAY()),10),11,12),IF(MONTH(TODAY())=2,IF(TODAY()<DATE(YEAR(TODAY()),MONTH(TODAY()),10),12,MONTH(TODAY())-1),IF(TODAY()<DATE(YEAR(TODAY()),MONTH(TODAY()),10),MONTH(TODAY())-2,MONTH(TODAY())-1)))

And create the measure using Dax below.

Measure 3 = IF(MONTH(TODAY())=1||MONTH(TODAY())=2,CALCULATE(SUM(Table[Value]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())-1 &&Table[checkmonth]=MONTH(Table[Date]))),CALCULATE(SUM(Table[Value]),FILTER(Table,Table[checkmonth]=MONTH(Table[Date]))))

Regards,
Lydia

[pbapuji · Accepted solution]

Great, it's working now. we have to do the below correction in "Measure 3" (missing Year concatenation)

Measure 3 = IF(MONTH(TODAY())=1||MONTH(TODAY())=2,CALCULATE(SUM(Table[Value]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())-1 &&Table[checkmonth]=MONTH(Table[Date]))),
CALCULATE(SUM(Table[Value]),FILTER(Table,YEAR(Table[Date])=YEAR(TODAY())&&Table[checkmonth]=MONTH(Table[Date]))))

Thanks your help I never forgot .

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result (numbers that you are expecting as results).

[pbapuji]

Hi Ashish,

The entire report shouled be filter by only one month, that shouled be dynamic.

If current date(systemdate) <10th of this month, for example if the month is November(M) then the report should filter by september(M_2).

else the report shouled be filter by october(M-1). sorry for the delay.

please let me know if you need more clarification.

Thanks,