cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbapuji Helper I
Helper I

dynamic "timeperiod" report filter.

Capture_power bi.PNG

 

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

Thanks in Advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft
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.

View solution in original post

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

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
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
https://www.linkedin.com/in/excelenthusiasts/
pbapuji Helper I
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
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.
1.JPG2.JPG

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
Helper I

Re: dynamic "timeperiod" report filter.

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

View solution in original post

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

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors