DAX - How not to display future value in a chart when using running total ?

[Vander1981]

Hi,

I am stuck with a visualization problem.

I have got a running total measure that I want to display in a Quarterly Chart (Column chart) with a drill down by months.

I have a Sales Table with a column Date (Max Date is 01-07-2017 / 01-JULY-2017) and a calendar Table.

I am working with a Fiscal calendar : 1st month is APRIL

I tried to work around this with a SWITCH formula :

 Portfolio accounts 2017 RT% Qtr (PCI) =
SWITCH (
    TRUE;
    MAX ( 'Calendar'[Date] ) > MAX ( Sales[Date] ); BLANK ();
    [Portfolio accounts 2017 RT% (PCI)]
)

For a reason that I cannot understand it only displays Q1 and not Q2 ?

Any idea ?

Thanks a lot

 

[gazpage]

Create two measures one each for the max calendar and sales dates. Drop them in your viz and you should soon see what is going on.

 

[Vander1981]

Okay good idea !

 

[Vander1981]

Create two measures one each for the max calendar and sales dates. Drop them in your viz and you should soon see what is going on.

Now, I understand what's the problem.

Here is the solution I found

Portfolio accounts 2017 RT% Qtr (PCI) =
VAR MAXSALES =
    MAX ( Sales[Date] )
VAR MAXCALENDAR =
    CALCULATE ( MIN ( 'Calendar'[Date] ); 'Calendar'[Fiscal Year] = 2017 )
RETURN
    SWITCH (
        TRUE;
        MAXCALENDAR > MAXSALES; BLANK ();
        [Portfolio accounts 2017 RT% (PCI)]
    )