Forum Discussion

masplin's avatar
masplin
Impactful Individual
8 years ago
Solved

Filtering calculation using values in Pivot table

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

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      masplin
      Impactful Individual

      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

      • Anonymous's avatar
        Anonymous
        Not applicable

        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