cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
alibek
Frequent Visitor

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. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
Super User IV

Re: Automate new measure creation

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 Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
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
Proud to be a Super User! Linkedin


View solution in original post

Super User I
Super User I

Re: Automate new measure creation

@alibek 

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:

period index.JPG

 and the model:

2020-02-13 (7).png

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)

Current month.JPG

 

 

or to have the last 12 months shown automatically:

InkedLast 12 months_LI.jpg

 

Or to filter whole pages (where you aren't comparing values outside the selected month)

Page filter 1.jpg

 

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...




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

5 REPLIES 5
Super User I
Super User I

Re: Automate new measure creation

@alibek 

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...)




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Super User IV
Super User IV

Re: Automate new measure creation

What measure you are creating and why it need to be created every month

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
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
Proud to be a Super User! Linkedin


alibek
Frequent Visitor

Re: Automate new measure creation

@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. 

Super User IV
Super User IV

Re: Automate new measure creation

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 Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
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
Proud to be a Super User! Linkedin


View solution in original post

Super User I
Super User I

Re: Automate new measure creation

@alibek 

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:

period index.JPG

 and the model:

2020-02-13 (7).png

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)

Current month.JPG

 

 

or to have the last 12 months shown automatically:

InkedLast 12 months_LI.jpg

 

Or to filter whole pages (where you aren't comparing values outside the selected month)

Page filter 1.jpg

 

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...




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors