Avoiding filtered context with Variable. Help needed

[danielboi]

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 simplified) Structure as following:

• PERIOD ID (Date) - The month/year of the reported value
• Corporate Entity - The various plants
• Report Item - Line item of the the PL, e.g. Sales, Gross Margin, R&D Cost etc
• VERSION ID - Defines if the row describes Actual, Budget or Forecast
• Value - Values in EUR

Values for Budget are covering on a monthly level the whole year, so we have periods from January 2021 to December 2021.

Values for Actual are only covering the past periods, so as of now from January 2021 to February 2021.

The users cam use a slicer to define the Periods they want to look at. Either a single month or any time frame they wish.

The report shows the actual numbers as well as a delta calculation to budget. 

My problem is that if the users changes the slicer to show January to May, the budget will be calculate YTD and then compared with the actual of YTD Feb (as only these two months are available for actual.)

I tried to intercept this possible problem, by extracting the maximum date of the PERIOD ID column with a filter on the column for the VERSION ID. (Otherwise it will show me December 2021 because there is budget data available.)

Used two approaches with Filter and Calculate and both give me exactly the date I expect. The problem is that they also put everything into the context of filtered version "Actual". This leads to that I only get the Financial Report values for actual.

BTW, the formula below considers intercompany consolidation, this is why it is a bit more complex.

All Values Cons MaxDate =

VAR VersionIDActualonly =

FILTER(

FactFIRE,

FactFIRE[Version ID]=2

)

RETURN

VAR DateMax =

maxx(

VersionIDActualonly,

FactFIRE[Period ID]

)

RETURN

CALCULATE(

[All Values Uncons],

FILTER(

FactFIRE,

not(

CONTAINS(

DimHierarchy,

DimHierarchy[CPOC],

FactFIRE[PCPOC]

)

)

),

FactFIRE[Period ID]<=DateMax

)+0

Any suggestions what to do to extract the maximum date for the Actual version? If I replace the Variable manually with February 2021 everything works fine. So I assume it is because of forwared fitler context of the VAR.

Thanks in advance.

Dan

[danielboi]

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]

@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]

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 · Accepted solution]

@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]

Yesssssss!!!!!! You did it.

This did the trick.

Meanwhile I also found a solution to create a separate table with the needed date, but I like this much better 🙂

[amitchandak]

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

You can easily get YTD like this for actual or budget

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

YTD till today =
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 ) )

YTD till next month(may) =
var _min = date(year(today()),1,1)
var _max = eomonth(today(), 1)
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

[danielboi]

1 more remark. If I create a measure extracting the max date and put it into a matrix visual it looks like this:

If I change the filtered version to FC it looks like this: 

The Dax:

MaxDate =

   VAR VersionID2only =

      FILTER(

         FactFIRE,

         FactFIRE[Version ID]=3

      )

   RETURN

maxx(

   VersionID2only,

   FactFIRE[Period ID]

)