cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Two measures - One uses the current month, the other uses previous months

Hi,

 

I've been struggling on this problem for a while now. I want to create a measure that uses two other measures. I want it to show a certain measure in the current month and in all the previous months show another measure

 

I want the yellow line to show the previous months, and the red line to only show the previous months

Targets.PNG

 

This is the code I've current got but this isn't working. This code only shows the [Target] measure for every month and not the [WBAH Target] measure

IsCurrentMonth = 
IF (
    MONTH ( SELECTEDVALUE ('Full Date'[Full Date])) = MONTH ( NOW () )
 && YEAR ( SELECTEDVALUE ('Full Date'[Full Date])) = YEAR ( NOW () ),
    [Target],
    SELECTEDVALUE('Targets- WBAH'[Target], 'Targets- WBAH'[WBAH Target]) ) 

  

Any help is welcomed

 

Thanks,

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Two measures - One uses the current month, the other uses previous months

If so, there there might be some issue in calculating whether or not you're in the current month

VAR is_current = selected_Month = cur_month && selected_year = cur_year

try

VAR is_current_month = selected_month = cur_month

VAR is_current_year = selected_year = cur_year

and return those and see what you get

you can then do

VAR is_current = AND(is_current_month,is_current_year)





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

14 REPLIES 14
Highlighted
Super User IV
Super User IV

Re: Two measures - One uses the current month, the other uses previous months

If you can use calendar date and time intelligence, It will give you prior measure easily.

Refer

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 year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

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 Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
Super User II

Re: Two measures - One uses the current month, the other uses previous months

IsCurrentMonth = 
IF (
    MONTH ( SELECTEDVALUE ('Full Date'[Full Date])) = MONTH ( NOW () )
 && YEAR ( SELECTEDVALUE ('Full Date'[Full Date])) = YEAR ( NOW () ),
    [Target],
    SELECTEDVALUE('Targets- WBAH'[Target], 'Targets- WBAH'[WBAH Target]) ) 

For debugging sometimes it helps to use VARs

IsCurrentMonth =

VAR cur_month = MONTH(NOW())

VAR cur_year = YEAR(NOW())

VAR selected_Month = SELECTEDVALUE('Full Date'[Full Date])

VAR selected_Year = SELECTEDVALUE('Full Date'[Full Date])

VAR is_current = selected_Month = cur_month && selected_year = cur_year

VAR WBAH_target = SELECTEDVALE('Targets-WBAH'[Target], 'Targets-[WBAH Target])
RETURN IF(is_current,[Target],WBAH_target)

Then you can change what you RETURN and see what values are really being selected... instead of just seeing the result. Sometimes you put the data in a table visual to make it easier to see.


am I reading SELECTEDVALE('Targets-WBAH'[Target], 'Targets-[WBAH Target]) correctly... it looks like its returning the [Target], and the WBAH Target is only the default value to be returned if it finds no [Target] ?

 

I'm a personal Power Bi Trainer I learn something every time I answer a question. I blog at http://powerbithehardparts.com/

The Golden Rules for Power BI

  1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
  2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
  3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Highlighted
Post Patron
Post Patron

Re: Two measures - One uses the current month, the other uses previous months

Hi,

 

Thanks for that, appreciated. I have given that a go and it is only returning the value of WBAH Target for all of the months, current and all the previous. 

 

WBAH Target is a target that is manual given to me at the start of every month and is the target for the month. That is based on a certain formula. Target is a formula based on a equasion and as there isn't enough data in the system for that equasion to be effective I have to used WBAH Target for the first month. Does that make sense?

 

Here is what the targets currently look like 

Target 2.PNG

Green = New measure that we're working on

Red = Formula measure

Black = Manual Target

 

The figures for the Green column should be. 

Jan 2020 - 36 

Dec 2019 - 20

Nov 2019 - 31

Oct 2019 - 31

etc

 

Does that make sense?

 

Thanks,

Mike

Highlighted
Super User II
Super User II

Re: Two measures - One uses the current month, the other uses previous months

If so, there there might be some issue in calculating whether or not you're in the current month

VAR is_current = selected_Month = cur_month && selected_year = cur_year

try

VAR is_current_month = selected_month = cur_month

VAR is_current_year = selected_year = cur_year

and return those and see what you get

you can then do

VAR is_current = AND(is_current_month,is_current_year)





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

Highlighted
Post Patron
Post Patron

Re: Two measures - One uses the current month, the other uses previous months

Hi,

 

Thanks for that, give it a go and I've still got the same results

Target 3.PNG

 

Current Month = 
var cur_month = MONTH(NOW())
var cur_year = YEAR(NOW())
var selected_month = SELECTEDVALUE('Full Date'[Full Date])
var selected_year = SELECTEDVALUE('Full Date'[Full Date]) 
var is_current_month = selected_month = cur_month
var is_current_year = selected_year = cur_year
var is_current = AND(is_current_month, is_current_year)
var WBAH_Target = SELECTEDVALUE('Targets- WBAH'[Target], 'Targets- WBAH'[WBAH Target]) 
return IF(is_current_month,[Target], WBAH_Target) 

 

I'm actually wondering if it's something to do with my Date Dimension table. I current have my date dimension table from January 2017 - December 2020, so maybe it's looking at the tables and thinking that we're not in the current month. 

Date Dimen.PNG

Maybe I need to change to theory of this and have most recent month in the target rather than current month

 

What are your thoughts? 

Mike

Highlighted
Super User IV
Super User IV

Re: Two measures - One uses the current month, the other uses previous months

Try using time intelligence function datesmtd or total mtd

 

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 year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
 


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-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 Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Patron
Post Patron

Re: Two measures - One uses the current month, the other uses previous months

Hi @amitchandak 

 

How can I use that in the context of my problem?

 

Thanks,

Mike

Highlighted
Post Patron
Post Patron

Re: Two measures - One uses the current month, the other uses previous months

Hi @amitchandak  @kentyler ,

 

Still stuck on this issue. Any other suggestions?

 

Thanks,

Mike

Highlighted
Super User II
Super User II

Re: Two measures - One uses the current month, the other uses previous months

Want to do a screen share and look at it together ? Sometimes its easier to get a result that way. I'm free til 9:00 PST and then after 10:00

Send me your email and a I'll send you a Zoom meeting invitation.

ken@8thfold.com





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors