Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How to achive the above senario, please help me any one on this.
Thanks in Advance.
Solved! Go to 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
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 .
@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,
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
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 .
Hi,
Share a dataset and show the expected result (numbers that you are expecting as results).
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |