Prior year calculation not working filtering on measure

[Anonymous]

So here is my model:

model

So if I use this query:

DEFINE
    VAR columnFilter0 =
        TREATAS (
            { "**********", "************", "**********" },
            'Distributor'[Parent Distributor]
        )
    VAR columnFilter1 =
        FILTER ( KEEPFILTERS ( VALUES ( 'Time'[Year] ) ), ( 'Time'[Year] = 2019 ) )
    VAR measuresFilter =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'Customer'[CDSId],
                    'Distributor'[Parent Distributor],
                    'Time'[Year],
                    columnFilter0,
                    columnFilter1,
                    "Cases", [Cases],
                     "Cases PY", CALCULATE([Cases PY], all('time'[Date]))
                )
            ),
            ( [Cases] < 1000 )
        )
EVALUATE
TOPN (
    200,
    SUMMARIZECOLUMNS (
        ROLLUPADDISSUBTOTAL (
            ROLLUPGROUP (
                'Customer'[CDSId],
                'Distributor'[Parent Distributor],
                'Time'[Year]
            ),
            "total"
        ),
        columnFilter0,
        columnFilter1,
        measuresFilter,
        "Cases", [Cases],
        "Cases PY", CALCULATE(Sum('sale'[OCases]),DATEADD('Time'[Date], -1 ,YEAR))
    ),
    [total], 0
)
ORDER BY [total] DESC

my results look like this:

data Prior year

I loss my prior year for some reason. If i remove the measure filter it i get prior year back. If I remove distributor i get it back. If i remove distributor and put something form like item table it still does not have prior year. if i remove the customer table fields it goes back to working.

I think its something to do with the wired many to many i made when denormlizing my customers but I'm unsure.

Prior year Clculation

CALCULATE([Cases],SAMEPERIODLASTYEAR('Time'[date]))

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

I see how to do To Date measures in your post but can't apply really to how i'm trying to calculate py. I need a sum based on the picked time filter and then for py is the picked time filter 1 year prior. so it can span between years 2018-2019

[amitchandak]

For the date filter, you can use the option given in the article and then calculate you can calculate prior year based on the Min or Max date of the slicer.

Current Sales = ( 
VAR _Cuur_start = Generator[Report Start Date]
VAR _Curr_END = Generator[Report End Date]
return 
calculate(sum(Sales[Sales]),OrderTime[Order Date] >= _Cuur_start && OrderTime[Order Date] <= _Curr_END )
)

Generator in this example is the same date in the article.

With Date Joined with Table

Sales Last Year = CALCULATE(sum(Sales[Sales]),OrderTime[Order Year]= CONCATENATE( Year(NOW())-1,""))
2nd last year = CALCULATE(sum(Sales[Sales]),OrderTime[Order Year]= CONCATENATE( Year(NOW())-2,""))

In case of slicer use Min function put that in a variable.

Article

https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613

[Anonymous]

I tried putting in the min and max dates and still nothing on the py calculation. 

[amitchandak]

Can you please share the formula.

[Anonymous]

I found this to gives a result back have not confirmed if this is produces correct numbers

Cases PY = CALCULATE (
            [Cases PY],
            ALL ( Sale ),
            columnFilter1
        )