Filtering calculation using values in Pivot table

[masplin]

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

[v-shex-msft · Accepted 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

[masplin]

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

[v-shex-msft]

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