Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to filter the last 6 months, so today in December, it should show Jul, Aug, Sep, Oct, Nov, Dec.
However, if I use date filter "in the last 6 months", it starts from Jun and cuts days with the days that have passed in December.
How do I make a filter that will start from 1st of Jul and dynamically change every next month to the 1st of Aug, then Sep, etc.
Solved! Go to Solution.
Hi @carnage66 ,
I have built a Calendar table by:
Table = CALENDAR(DATE(2020,9,1),TODAY())
Now please try to create a flag measure:
Flag = IF(MAX('Table'[Date])> EOMONTH(MAXX(ALL('Table'),[Date]),-6),1,0)
And apply it to filter pane:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey, sorry for the late reply , there is simple way to get the last 6 months data with or without current month ,and we don't need to write any DAX measure for this. we can do the changes in the PBI Desktop only.
first select the table then go on page level filter -->drag your calendar column --> refer below
by choosing months it will dispaly until current month
by choosing calender month it will display the completed months
like this you can choose for years also
if it helped please save the solution and ACCEPT IT AS SOLUTION please
Hi @carnage66 ,
I have built a Calendar table by:
Table = CALENDAR(DATE(2020,9,1),TODAY())
Now please try to create a flag measure:
Flag = IF(MAX('Table'[Date])> EOMONTH(MAXX(ALL('Table'),[Date]),-6),1,0)
And apply it to filter pane:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-eqin-msft : Here i have the fiscal month column containing the data like FY22JAN , i need to use fiscal month column only to show the months in table visual, and the fiscal month column data type is in text format. in this case how can i acheive to display last six months except the current month. please help me to resolve this. i need the solution immediately as soon as possible.
Note: The date table is not in the hirarchey format.
@carnage66 , Last 6 month of data based on selected date
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))
Based on today
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today(),-6,MONTH))
you can switch based on isfiltered
But if you need trend based on selected date you need independent table
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hi,
Could you please tell is it posssible to show sum of last 6 months if in the table I chose only 1 year and month, to avoid of "based on selected date".
Thanks!
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |