Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi there,
I have a calendar table that has fulldate (year) and isPastMonth calculated column.
IsPastMonth = if Date.Month([#"FullDate"]) < Date.Month(DateTime.LocalNow()) then "Y" else "N"
How would i make the IsPastMonth show the past months of the year i selected? I have a fulldate (year) slicer that currently show 2019 and 2020.
Do i have to create another measure of calculated column?
Thank you
Glen
Solved! Go to Solution.
@dax @Anonymous ,
I was able to fix my problem by modifying the IsLastMonth calculated column as follows:
Old Value:
IsLastMonth = if Date.Month([#"FullDate"]) < (Date.Month(DateTime.LocalNow()) then "Y" else "N"
New Value:
IsLastMonth = if (Date.Month([#"FullDate"]) < (Date.Month(DateTime.LocalNow()))) or (Date.Year([#"FullDate"]) < (Date.Year(DateTime.LocalNow())))
then "Y" else "N"
This allowed the whole 2019 to be past month and January for 2020.
Thank you
Glen
Hi @gco ,
I think you could use filter to achieve this. You could use slicer to filter year, then you could add IsPastMonth in visual or page filter, then choose "Y " in it which will show the past months of your selected year.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have this on my report, but for some reason, it shows only January for both 2020 and 2019. I wanted to be able to display the whole months of 2019, and only January for 2020.
Thanks
Glen
@dax @Anonymous ,
I was able to fix my problem by modifying the IsLastMonth calculated column as follows:
Old Value:
IsLastMonth = if Date.Month([#"FullDate"]) < (Date.Month(DateTime.LocalNow()) then "Y" else "N"
New Value:
IsLastMonth = if (Date.Month([#"FullDate"]) < (Date.Month(DateTime.LocalNow()))) or (Date.Year([#"FullDate"]) < (Date.Year(DateTime.LocalNow())))
then "Y" else "N"
This allowed the whole 2019 to be past month and January for 2020.
Thank you
Glen
Hi @gco ,
There should not be much else to do.
Depending on an approach you can either add a new column (Year) in the dataset and create a slicer from this column (and Y/N IsPastMonth filter on the visual containing "months") or you can go with grouping FullDates into years - I went with creating a group on the slicer visual binned to years.
The key, I guess, is to add the IsPastMonth filter to the either report, page or visual filters (just drag&drop in the filters section and set to "Y").
Kind regards,
JB
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |