Forum Discussion
Advocate IICreate a Demo Date and Time based on Today() and DateTime.LocalNow()
EDIT: I need to set a FIXED DATE and a FIXED DATETIME in a variable as though I was showing the system from 18months ago ... I don't need a slider that will cause chaos and confusion to end users. It needs to be invisible to me that I set once for a live system ... i.e. set to Today() and LocalNow() and alternatively I can set a fixed date and time instead for a demo system. These variables are used in about 200 formulae in the system ... so I can't be changing each formula for each implementation. I do have a Calender Time Intelligence table in my platform ... so could use the date on a slider, but that's not what I am trying to achieve ... this is embedded across multiple formulae.
I thought this would be simple code - but seems to be something ridiculously complex to do.
----------------------------
I have a custom solution built with extensive demonstration data .... e.g. Timesheets which translate to costs. I want to be able to set 2 variables in my Power BI Dataset ... and then use those vairables in more than 200+ measures rather than the real time Today() and DateTime.LocalNow(). I've tried using Parameters in my Power Query, but I can't seem to reference those and they don't actually work to get real time and date.
Currently my measure would look like this:
- Measure 1 = Today() - 28
If I can set a variable to be:
- DemoDate = Today()
Then the measure can look like this.
- Measure 1 = DemoDate - 28
I need to do this for both:-
- DemoDate = Today()
- DemoTime = DateTime.LocalNow()
The reason for this is when I do a demonstration of the system I want to know the "demonstration date" since all the data is time sensitive, e.g. within a financial year that makes sense for the data etc.
Then when I use the system in a live deployment, I simply change DemoDate and DemoTime to the real formulae ... rather then the fake time/date.
How do I achieve this in Power BI?
7 Replies
- bhanu_gautam
Super User
sulleyinoz ,To achieve this in Power BI, you can use What-If Parameters to create dynamic date and time variables that you can reference in your measures. This approach allows you to set a demonstration date and time, which can be easily adjusted for live deployments.
- sulleyinoz
I'm trying to replace a variable date (i.e. LocalNow() or Today()) with a fixed date in a variable I can then use in my formulae / measures.
I've had a look at the Parameter for modelling ... but not sure how that would work for what I'm trying to achieve ... it doesn't make sense since I can't set up dates or formula in the parameter? Maybe I'm missing something ... but the two options Numeric Range and Fields don't apply. I want the parameter to be a replacement for a DATE not a number, and its not a field I'm replacing but a formula operator .. i.e. Today() or LocalNow().
Have you got an example of exactly how to do this step by step to clarify?
Thanks
- sulleyinoz
OK, seems to be some confusion here.
I already have a Calender Time Intelligence table in my platform ... so could use the date on a slider, but that's not what I am trying to achieve ... I'm using a reference to real date and time (i.e. Today() and LocalNow()) and this is embedded across multiple formulae.
I need to set a FIXED DATE and a FIXED DATETIME (System Demonstration Day) in a variable as though I was showing the system on that day from 18 months ago. I don't want or need a slider that will cause chaos and confusion to end users. It needs to be invisible to users and set by me once for a live system ... i.e. set to Today() and LocalNow() and/or alternatively I can set a fixed date and time instead for a demo system.
These variables are used in about 200 formulae in the system ... so I can't be changing each formula for each implementation.
I thought this would be simple code - but seems to be something ridiculously complex to do.
Surely someone has done this before ... ?
- sulleyinoz
Still don't have a reasonable answer for this one ... anybody?
- dk_dk
If you are able to add tables to your model, you could create a table with DAX, Power Query, or the Enter Data option, that would only contain one column and one row, with the value being set to the date you want to use in the demo. Make sure the column is in the correct date format.
Then you can make a DemoDate = MAX(Table[Column]), and reference that for the demo, and Today() for the live deployment.
Let me know if this works for you, or if I maybe misunderstood your use case?
- Chewdata
Responsive Resident
Not Sure if I fully understand the case, but maybe this can put in the right direction:
You can make a Power BI Parameter - numeric value -> name: Demodate, min = 0, max = 1, increment = 1. Then it will act like a sort of switch.
Then create measure:
DateToUse = IF(Demodate[Value] = 1, [DateOptionOne], [DateOptionTwo])- sulleyinoz
This is interesting ... I'll give this a try in the next day or two ... I'm hoping that I can use the LocalNow() and Today() in those formulae ... but I quite like this ... elegant if it works.
