cancel
Showing results for
Did you mean:
pbapuji Helper I

## dynamic "timeperiod" report filter. 2 ACCEPTED SOLUTIONS

Accepted Solutions Microsoft

## Re: dynamic "timeperiod" report filter.

@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.
pbapuji Helper I

## Re: dynamic "timeperiod" report filter.

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
Highlighted Super User IV

## Re: dynamic "timeperiod" report filter.

Hi,

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

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

## Re: dynamic "timeperiod" report filter.

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, Microsoft

## Re: dynamic "timeperiod" report filter.

@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.
pbapuji Helper I

## Re: dynamic "timeperiod" report filter.

but it's not working for if the month is January and February. Microsoft

## Re: dynamic "timeperiod" report filter.

@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.
pbapuji Helper I

## Re: dynamic "timeperiod" report filter.

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 .

Announcements #### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section. #### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights. Top Solution Authors
Top Kudoed Authors
Users online (1,549)