cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

@Anonymous 

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





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
Anonymous
Not applicable

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

@Anonymous 

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





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

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!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

amitchandak
Super User
Super User

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!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
PaulDBrown
Super User
Super User

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





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.






Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors