Measure From a LookUpValue filtered by a Measure

[zamak]

Hi,

My goal is to have a report where the users can select a date range and it populates a table with the sales, cost, gross margin, fiscal-year-to-date sales, fytd cost, and fytd gross margin grouped by the department. I have the grouping, sales, cost, and gm columns calculated and filtered by date-range all set, but I cannot figure out how to calculate the fytd.

I have a between date range slicer. I have a measure that takes the minimum value of that slicer and looks up the fiscal year for that date. I am running into an issue where every time I try to look up the first day of the fiscal year, it always comes back blank. This is my first time doing anything this advanced in Power BI and I may be misunderstanding how some of these things work. I have a feeling that the filtering from the slicer is preventing the lookup from finding the row, but even when I tried wrapping the table in the "ALL" function it didn't work.

This is the current state of the measure:
"FiscalYearStartDate = CALCULATE(LOOKUPVALUE(dateMapping[date], dateMapping[fiscal_year], [FiscalYear], dateMapping[fiscal_day], 1))"

Below is a picture of all the relationships in the model, where the dateMapping table holds all the fiscal calendar information and the slicer is applied to the calendar table.

I'm also doing all this in DirectQuery mode so I'm not sure if that could be affecting anything.

I appreciate the help!

[Anonymous · Accepted solution]

Hi @zamak ,

Please have a try.

Create a measure.

FiscalYearStartDate =
CALCULATE (
    MIN ( dateMapping[date] ),
    FILTER (
        ALL ( dateMapping ),
        dateMapping[FiscalYear] = SELECTEDVALUE ( dateMapping[FiscalYear] )
            && dateMapping[fiscal_day] = SELECTEDVALUE ( dateMapping[fiscal_day] )
    )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @zamak ,

Please have a try.

Create a measure.

FiscalYearStartDate =
CALCULATE (
    MIN ( dateMapping[date] ),
    FILTER (
        ALL ( dateMapping ),
        dateMapping[FiscalYear] = SELECTEDVALUE ( dateMapping[FiscalYear] )
            && dateMapping[fiscal_day] = SELECTEDVALUE ( dateMapping[fiscal_day] )
    )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zamak · Accepted solution]

Hi, thanks for getting back to me.

Unfortunately this didn't work, but I did manage to get what I want.

I deactived the relationships between these tables and it seems to have fixed it. This reaffirms my initial belief that the slicer was still filtering the dateMapping table even when it's wrapped in the "All" function.