I have a last refresh date column with Monthly, Quarterly and Yearly frequencies for an update frequency column. I need to show the next refresh date of the last refresh date column, using the update frequencies. Am familiar with using same period last year function but don’t think it would help to address this problem. I would really appreciate any help to write a DAX measure to answer the problem. Thank you
Hi @damoako
To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:
- Your work-in-progress PBIX file, using sanitized data if necessary
- here’s @BrianJ’s YouTube video on how to anonymize data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
- Your dataset as an Excel file (again, sanitized if necessary)
- A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.
Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.
Greg
Attached is a screen shot. Unfortunately I could not copy the table to provide the PBIX file. The other missing table to this is just the date table. The Update frequency table also includes Monthly, Quarterly and Yearly frequencies. Its for those I intend to show the next refresh date e.g. for what I like to see is if the last refresh for a Monthly frequency dataset is 1/26/23 then the next refresh should be somewhere around 2/26/23
Hi @damoako … a proper Excel mockup using sample data that shows some sample and its’ expected outcome would be an asset and would help the forum members pursue your issue.
Greg
Hi @damoako.
As with many things in Power BI, there are often multiple solutions to a problem. Here’s one:
After added the Enterprise DNA Extended Date Table as a [Dates] table and marking it as such, a single measure using SWITCH-TRUE was used to calculate the next refresh date:
Next Refresh Date =
VAR _LastRefresh = SELECTEDVALUE( Refreshes[Last Refresh Date] )
VAR _Frequency = SELECTEDVALUE( Refreshes[Update frequency] )
VAR _Result = SWITCH( TRUE(),
ISBLANK( _LastRefresh), BLANK(),
_Frequency = "Yearly", _LastRefresh + (365 / 1),
_Frequency = "Quarterly", _LastRefresh + (365 / 4),
_Frequency = "Monthly", _LastRefresh + (365 / 12),
BLANK()
)
RETURN
_Result
This solution handles the yearly, quarterly, and monthly frequencies mentioned in your issue. Additional conditions/calculations can be added to the SWITCH-TRUE statement to handle other frequencies as desired.
Hope it helps.
Greg
eDNA Forum - Next Refresh Date.pbix (149.7 KB)
Thank you so much Greg for this solution and also for your patient with me.
What is the best and efficient way to learn DAX expression. I wish I could do something like this on my own but its taking me forever to learn
No problem … we all started sometime … I’d do the 2 free Enterprise DNA “Ultimate Beginner’s Guide …” courses, then more if you hopefully have an eDNA membership … otherwise, just practice-practice-practice …
Greg