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.
Hi all,
I'm breaking my mind here! Could really use some help. Am trying to simplify this logic I have to change the end of the month date essentially.
We have a program in the business that sends out surveys asking for customer experience. We measure scores on a monthly basis but we close the month on the 15th of the next month. We do this so customers we interacted with in May can still answer their survey until the 15th of June.
I've created a dashboard that shows metrics for the current open month and I'm trying to get the dashboard to show data from May until we hit June 15th after which it should start showing June data. So a simple "in this month" logic wouldn't work.
My current working solution is very archaic and doesn't take into account year changes. Basically I have 3 columns in Power Query:
1. A column that determines whether the row of data is in "this month" or "previous month" based on today's date
2. A column that returns the day number for rows that have a value for "this month" or "previous month"
3. A "ForReporting = True" column when "this month" = current month or to show "previous month" if the day number < 15
I then just apply a filter to my page to only include my column 3 if it's true. HOWEVER, I feel there should be a much easier way to do this in a single query. Any help would be greatly appreciated!
Column 1
= Table.AddColumn(#"Renamed Columns", "Column 1", each if Date.Year([EventDate]) = Date.Year(DateTime.LocalNow()) and Date.Month([EventDate]) = Date.Month(DateTime.LocalNow()) then "Current Month" else if Date.Year([EventDate]) = Date.Year(DateTime.LocalNow()) and Date.Month([EventDate]) = Date.Month(DateTime.LocalNow())-1 then "Previous Month" else null)
Column 2
= Table.AddColumn(Custom3, "Column 2", each if [Column 1] <> null then Date.Day([EventDate]) else null)
Column 3
= Table.AddColumn(#"Added Custom6", "Column 3", each if [Column 1] = "Same Month" then "True" else if [Column 1] = "Previous Month" and [Column 2]<15 then "True" else null)
Two things to help you along. One, make yourself a Today=DateTime.LocalNow() variable somewhere that will compute once, as its own query, so you don't have to keep computing DateTime.LocalNow many times per expression.
But also, to refer to the next or prior 15 days next month my or last month, you can use expressions like
each if [Date]<= Date.StartOfMonth - #duration(15,0,0,0) then this thing else that thing
or each [Date] <= Date.EndOfMonth(Today) + #duration(15,0,0,0)
Hope this helps!--Nate
Thanks Nate, helpful suggestion on the variable, I've not worked with those yet but have a vague understanding what it is so will look to incorporate that.
On your solution though - I think I may have explained it poorly. The reporting is still done on a month basis, so I'm currently reporting on JUST the month of May. The dashboard just has a number related to May's results that I want to "switch" to June on the 15th of the month so I essentially want the visual to "wait" until the 15th of the next month to start showing next months' results.
So very simply put the logic today (1st of June) would need to be:
1. If [EventDate] = Today's Month then show Today's month else /*if it were 31st of May then I would want May results to be shown */
2. If [EventDate] = NextMonth BUT Day < 15 then show May results and if Day > 15 then show June /*Because it's June today but it's before the 15th, I'd still want the results to show May. On June 15th I'd want it to start showing June's results */
I must admit I don't fully understand the formulas you've listed but I suspect it tries to give the next or previous 15 days from today's date which is not quite what I'm after.
What about just adding one new column to capture the Reporting Date? Then in your visuals you can show results over time, or you can simply filter for the max Reporting Date in Power Query.
= Table.AddColumn(#"Changed Type1", "Reporting Date", each let NextMonth = Date.AddMonths ( [EventDate], 1 ) in #date ( Date.Year ( NextMonth ), Date.Month(NextMonth), 15 ), type date)
Hi, thanks so much for your reply, it's very helpful. I've tried this but am not sure if the result is what I'm after.
The general reporting is very easy, I just use the event date and add my metric and it gives me the results over time. In this case though I have a dashboard that is visible on a TV in the business. On this is a visual that as per today (1st of June) is supposed to continue to show May's results until the 15th of June, after which it will start showing June's results (up until the 15th of July etc).
So I'm essentially looking for a formula that will give me a column with "True" for May's records, until we hit the 15th of June, then I want that "True" to apply to June's results. I hope I'm making sense!
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.