Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
emdnz
Helper I
Helper I

Change end of month to 15th of day

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)

4 REPLIES 4
watkinnc
Super User
Super User

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

 

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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.

jennratten
Super User
Super User

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.  

jennratten_0-1654055079686.png

= 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! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors