Forum Discussion
Automate new measure creation
Hi,
I have a report which requires creation of new measures each month the report is updated.
The measures are basically the same, other than month and year parameters.
Is it possible to automate this process somehow or should I change the whole setup (if so, how?).
Thanks.
If you have date calendar then you can have measures like this using time intelligence. Unless you need month name
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-2,MONTH)))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-3,MONTH)))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on LinkedinAnonymous
There are many ways of doing it.
For example, if you create a period table linked to your calendar table, you can use a period index field to filter visuals or even whole pages in the filter pane, or in measures.
Here is an example:
1) period table ranked from most recent month backwards:
and the model:
This way the most recent month will always be ranked number 1, previous month 2, 2months back 3 etc...
2) For simple models, you can then use this period index in the filter pane:
Note: the measures displayed in the visuals have no date filters; they are direct sums or divisions; the filtering is carried out by the period index selection in the filter pane on visuals or the page itself
either on individual visuals (which aren't comparing with values from previous periods)
or to have the last 12 months shown automatically:
Or to filter whole pages (where you aren't comparing values outside the selected month)
For more "complex" calculations (Period comparisons etc..), you can use the period index in your measures to define specific periods. Similar to using Time Intelligence; just another way of approaching the challenge, and in some instances arguably simpler.
Since you have a calendar table, you can of course also use time intelligence if need be.
The advantage of this method is that you will always have the visuals updated for your current month page, previous month page etc...
As I say, it is one way (one of many) of going about this...
5 Replies
Anonymous
Can you post an example of the measures that need updating?
In general, you shouldn't need to "update" any measures (sweeping statement here...).
Month and Year parameters can be dynamic (for example using MONTH(TODAY()) or YEAR(TODAY()) etc...)
What measure you are creating and why it need to be created every month
amitchandak PaulDBrown
Each new measure is being created with a filter for new period, something like
newperiodmeasure = calculate([value], filter(calendar, calendar[period] = "February 2020"))
Then this measure is used for selection purposes for which there is a separate table (and which has to be updated too):currentperiod =
var selectedp = selectedvalue(selectiontable[selected period])
return
switch(
true(),
selectedp = "December 2019", [decembermeasure],selectedp = "January 2020", [januarymeasure],
selectedp = "February 2020", [newperiodmeasure])
Please share any suggestions on how to improve this setup.
Thanks.
If you have date calendar then you can have measures like this using time intelligence. Unless you need month name
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-2,MONTH)))) last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-3,MONTH)))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on LinkedinAnonymous
There are many ways of doing it.
For example, if you create a period table linked to your calendar table, you can use a period index field to filter visuals or even whole pages in the filter pane, or in measures.
Here is an example:
1) period table ranked from most recent month backwards:
and the model:
This way the most recent month will always be ranked number 1, previous month 2, 2months back 3 etc...
2) For simple models, you can then use this period index in the filter pane:
Note: the measures displayed in the visuals have no date filters; they are direct sums or divisions; the filtering is carried out by the period index selection in the filter pane on visuals or the page itself
either on individual visuals (which aren't comparing with values from previous periods)
or to have the last 12 months shown automatically:
Or to filter whole pages (where you aren't comparing values outside the selected month)
For more "complex" calculations (Period comparisons etc..), you can use the period index in your measures to define specific periods. Similar to using Time Intelligence; just another way of approaching the challenge, and in some instances arguably simpler.
Since you have a calendar table, you can of course also use time intelligence if need be.
The advantage of this method is that you will always have the visuals updated for your current month page, previous month page etc...
As I say, it is one way (one of many) of going about this...