Forum Discussion

danielboi's avatar
danielboi
Helper I
5 years ago
Solved

Avoiding filtered context with Variable. Help needed

Hello everybody,   making good progress with my Power Bi and now in fine tuning mode. I am building a financial report which is based on a download from our corporate data warehouse. (Very simplifi...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    danielboi , In the above I see one change , if that can work for you

     

    VAR DateMax =
    CALCULATE(
    MAXX(
    filter(allselected(FactFIRE),
    FactFIRE[Version ID]=2)    ,
    FactFIRE[Period ID]
    )
    )
    RETURN

danielboi's avatar
danielboi
Helper I
5 years ago

Hello amitchandak ,

 

I am aware of the YTD formulas, but as far as I know it would not allow me to use a slicer to look at any combination of periods as needed, e.g. only Q2 or June and Juli. Will try strip down the file, but might take a while.

 

At the end of the day I am just trying to extract the date without a filter context on the version (act/bud/fc)

 

Have a great day.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    danielboi , I shared on formula based on today, where eomonth move month up and down,

     

    same way maxx(allselected('Table'), 'Table'[Date]) , can replace today. 

     

    YTD till today =

    var _today = maxx(allselected('order'), 'Table'[order]) 
    var _min = date(year(_today),1,1)
    var _max = eomonth(_today, 0)
    return
    CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

     

    In this case and today's case, you should have date selected or use all on the date table 

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • danielboi's avatar
      danielboi
      Helper I
      5 years ago

      Hello amitchandak,

      thanks.  Highly appreciated your effort. My question is not so much going into the direction how to achieve to show certain numbers, but more fundamental. How to avoid, that my attempt to filter for the max date of actual version is not going to pass on a filter context only showing actual numbers.

       

      Possible that my entire approach is not very efficient, but I want to go live with V1 next week and this seems to be the remaining hurdle to increase the user experience (or rather avoid user confusion)

       

      By replacing the MaxDate at the very bottom with a hard coded date I get exactly what I want. So I assume that my way of extracting the date is wrong or I need to add something to the final calculate formula to eliminate the filter context generated with my variable

       

      All Values Cons MaxDate =
        VAR DateMax =
          CALCULATE(
            MAXX(
               FactFIRE,
               FactFIRE[Period ID]
            ),
            FactFIRE[Version ID]=2
         )
         RETURN

       

      CALCULATE(
         [All Values Uncons],
         FILTER(
            FactFIRE,
               not(
                  CONTAINS(
                     DimHierarchy,
                     DimHierarchy[CPOC],
                     FactFIRE[PCPOC]
                  )
               )
            ),
            FactFIRE[Period ID]<=DateMax
      )+0

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        danielboi , In the above I see one change , if that can work for you

         

        VAR DateMax =
        CALCULATE(
        MAXX(
        filter(allselected(FactFIRE),
        FactFIRE[Version ID]=2)        ,
        FactFIRE[Period ID]
        )
        )
        RETURN