Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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.






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

amitchandak
Super User
Super User

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

 

PaulDBrown
Community Champion
Community Champion

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.