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.
Our budget has been defined as monthly numbers and exisits in a table where the budget number is on a row where the date is 1/mm/yyyy. This date is then related to my main date table so can be plotted as months, qtr, year etc.
I have been asked to create a report comparing our actual to the daily budget instead of the monthly version. Monthly is easy because the budget dates and date table have matching entries. So my problem is for each row in my pivot which has a day from my date table, find the monthly budget figure for the associated month and then say divide by days in the month. I'm struggling to work out how to use the date on the pivot table to generate the value of the monthly budget?
This is compeltely wrong but in principal I thought it might work althoguh have never use SELECTED VALUE before
Fidn the value of Budget GP (which is a measure just adding net sales and CoS on the Detail_Budget_16 table) where the date in the pivot table is change to the 1st of the same month and therefore will have a matching month value in the budget table. However produces a blank.
Any tips on where to start much appreciated
Mike
Budget GP Current Month = CALCULATE( [Budget GP], FILTER( Detail_Budget_16, DATE(YEAR(SELECTEDVALUE(DateTable[Day])),MONTH(SELECTEDVALUE(DateTable[Day])),1)= Detail_Budget_16[Month] ) )h
Solved! Go to Solution.
HI @masplin,
It will be help for analysis if you share some sample data.
In addition, you can also try to use below formula if it works for your scenario:
Budget GP Current Month = VAR current_date = MIN ( DateTable[Day] ) RETURN CALCULATE ( [Budget GP], FILTER ( Detail_Budget_16, YEAR ( Detail_Budget_16[Month] ) = YEAR ( current_date ) && MONTH ( Detail_Budget_16[Month] ) = MONTH ( current_date ) ) )
Regards
XIaoxin Sheng
HI @masplin,
It will be help for analysis if you share some sample data.
In addition, you can also try to use below formula if it works for your scenario:
Budget GP Current Month = VAR current_date = MIN ( DateTable[Day] ) RETURN CALCULATE ( [Budget GP], FILTER ( Detail_Budget_16, YEAR ( Detail_Budget_16[Month] ) = YEAR ( current_date ) && MONTH ( Detail_Budget_16[Month] ) = MONTH ( current_date ) ) )
Regards
XIaoxin Sheng
So if i have apivot table with severla dates on say the columns witll the Min function slelect that date for each column as the current_date?
If so then yes this is what i'm looking for thanks
Hi @masplin,
For single row contents, min function will return current date.
If it works on summary row content, it will return the min date from the summary records.
Regards,
Xiaoxin sheng
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |