Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Solved! Go to 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)
Help when you know. Ask when you don't!
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?)
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
Ok, great to hear!
Proud to be a Super User!
Paul on Linkedin.
Forgot to @ you. My email is: michael.knight@ohconsult.co.uk
I'm free from now until 10am PTS
Thanks
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
Help when you know. Ask when you don't!
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
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
Help when you know. Ask when you don't!
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
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)
Help when you know. Ask when you don't!
Hi,
Thanks for that, give it a go and I've still got the same results
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |