Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi guys,
I hope someone can help me, since I'm not the best (yet..) in combining formulas.
I just need to filter a date column in a visual on all dates untill end of the current month. It is not possible to use a simple filter option, since the most accurate option is to choose recent months untill today, but then I don't have all of the month. So, I need to write a measure/calculated column for it.
It doesn't matter if it is a calculated column with yes/no or a measure with a certain value that I can use in my visual filtering. As long as I can filter my visual based on the past untill end of this month.
I look forward to responses.
Kind regards,
Manon
Solved! Go to Solution.
@ManonNL - You can fix the blank issue easily with this:
SWITCH( TRUE(),
ISBLANK( [Date] ), "No",
[Date]<=EOMONTH(TODAY(), 0),
"Yes", "No"
)
If this works, please accept as the solution, it helps with visibility for those with the same problem.
hi @ManonNL ,
then it should be like:
what mark_endicott proposed shall also work.
Hay @ManonNL
If you have a date table created in the Query Editor with M Code then you can add a column in to the table like this:
= if Date.IsInCurrentMonth([Date]) then "Current Month"
else if Date.From([Date]) < Date.From(DateTime.LocalNow()) then "Previous Month"
else "Future Month"
Then use this field in date table as a filter on your visual.
My date column was a calculated column. But thanks for your response, I'm sure this will come in handy for other cases.
hi @ManonNL ,
you may add a calculated column like:
Thank you so much! It almost helps entirely, but blank values give "Yes" now. Do you know what I can add to make blank values No?
hi @ManonNL ,
then it should be like:
what mark_endicott proposed shall also work.
@ManonNL - You can fix the blank issue easily with this:
SWITCH( TRUE(),
ISBLANK( [Date] ), "No",
[Date]<=EOMONTH(TODAY(), 0),
"Yes", "No"
)
If this works, please accept as the solution, it helps with visibility for those with the same problem.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
12 | |
12 | |
9 |
User | Count |
---|---|
63 | |
22 | |
21 | |
17 | |
11 |