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
michael_knight
Post Prodigy
Post Prodigy

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

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
PaulDBrown
Community Champion
Community Champion

@michael_knight 

Apologies for butting in mid discussion, but I don't follow what you mean by:

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

(it reads like you want both lines to show the same thing, doesn't it?)





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.






Hi @PaulDBrown 

 

No need to apologies. I appreciate all the help possible. Ken and Myself solved the issue on a Zoom live chat. 

 

Cheers,

Mike

@michael_knight 

Ok, great to hear!





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.






michael_knight
Post Prodigy
Post Prodigy

@kentyler 

 

Forgot to @ you. My email is: michael.knight@ohconsult.co.uk

 

I'm free from now until 10am PTS 

 

Thanks

michael_knight
Post Prodigy
Post Prodigy

Hi @amitchandak  @kentyler ,

 

Still stuck on this issue. Any other suggestions?

 

Thanks,

Mike

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


Yes, that will be perfect. 

 

I just set up an account then, I haven't used Zoom before. I believe I just added you to my contacts on there?

 

Thanks

kentyler
Solution Sage
Solution Sage

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


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

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


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

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

Hi @amitchandak 

 

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

 

Thanks,

Mike

amitchandak
Super User
Super User

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

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.