cancel
Showing results for
Did you mean:
Helper I

## dynamic "timeperiod" report filter.

2 ACCEPTED SOLUTIONS
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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 .

6 REPLIES 6
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I
but it's not working for if the month is January and February.
Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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 .

Super User III

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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,

Announcements