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

@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

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
v-yuezhe-msft
Microsoft
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.
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.

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.

@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

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

Ashish_Mathur
Super User III
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
https://www.linkedin.com/in/excelenthusiasts/

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,

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors