Hiding past and future dates in a table that has no dates based on max and min date in another table

[SV_community]

Hi

We have a budget table that has no date attribute. THere is a fixed budget for a project. This is strictly a fact table that contains several budget attributes like Original, Current and Projected Budget. There is a requirement from business to be able to plot expenses/cost and the budget in the same table or graph. The cost table (again a fact table) has the posted date that is connected to calendar table.

So when I plot budget measure and expense measures together, the expensese plot fine because they have a date on that table. The Buget starts from the earliest date in Calendar table and goes well out in the future (expected as there is no relationship with calendar table).

I have been told to not add any calculated columns in the model. I tried this approach documented here. This helps me to avoid showing future dates but dates much before the min (post date) show up.

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

Here is what I have tried so far:

GC Current Budget Cost = VAR LastDayAvailable =
CALCULATE (
MAX ( 'Expense'[Ref Date] ),
ALL ( 'Calendar' )
)
VAR FirstDayInSelection = Min('Calendar'[Date])
VAR ShowData =
(FirstDayInSelection <= LastDayAvailable)
VAR Result =
IF (ShowData,[GC Current Budget Cost])

RETURN Result

When I replace the FirstDayinSelection variable=CALCULATE (
MIn ( 'Expense'[Ref Date] ),
ALL ( 'Calendar' )
), then in fact I end up with both past and future dates showing up

I tried using Datesbetween but I cannot use them with varaibles

[parry2k]

@SV_community what is the actual issue? What you are trying to achieve? Share sample data and expected output.

[SV_community · Accepted solution]

I tweaked the code and it works now. The Var ShowData was only filtering future dates.

GC Current Budget Cost 2 = VAR LastDayAvailable =
    CALCULATE (
        MAX ( 'Job Cost Detail'[Ref Date]),
        ALL ( 'Calendar' )
    )
VAR FirstDayInCalendar =
    MIN ( 'Calendar'[Date] )
Var  MinDayAvailable=
CALCULATE(MIN('Job Cost Detail'[Ref Date]),all ('Calendar'))
VAR ShowData =
    (FirstDayInCalendar<= LastDayAvailable && FirstDayInCalendar>=MinDayAvailable)
VAR Result =
    IF (
        ShowData,
        CALCULATE (
            [GC Current Budget Cost]
        ))
   
RETURN Result