How to get same month last year data to populate when using a second date drilldown?

[rlupe]

Hi,

I am attempting to build a PBI that looks at ordering data based on the date an order was created, but I want to see the month that it was billed in. My 'This Year' data is correct, but my 'Last Year' data won't populate into all the months, and what does populate isn't correct. I've been trying to get this to work for over a month, is it possible to do this in PBI?

For example, if I drilldown to 2022, the order create date can anytime during 2022, while the billing date can be in 2022 or even 2023. See screenshots for how it currently looks vs expected. 

Current:

Expected:

I am using the sameperiodlastyear, but maybe in the wrong way or need additional parameters in the formula. I posted links below for the pbix and expected output. I have tried some variations of Selected( and All( - but not much experience in PBI so no luck.

This is the formula I am using: 

Bulletin (LY) = calculate(sum('Detail Table'[Bulletin]),SAMEPERIODLASTYEAR('Date Table'[date]))

PBIX Link: https://drive.google.com/file/d/1KzrXuJL6aVmNeSagQyKxVKr1Tl5sC_nz/view?usp=sharing

Expected Output Link: https://docs.google.com/spreadsheets/d/18EoUMU-QkyzHFBPfJ5VciH1RTTkYQlH3/edit?usp=sharing&ouid=11603... 

[sergej_og]

If i have understood the case correctly then it is a case for USERELATIONSHIP to CALCULATE ordered value versus billed value. I have had this kind of usecases in the past.
https://dax.guide/userelationship/

[rlupe]

I am not sure I know how to apply this. There is only one $ value column, this is a year over year calculation. But there are two dates (create and billing). I want to see the value in the billing date, but I also want to be able to drill into a single year from the order create date. The sameperiodlastyear isn't working, I think because 2 dates are involved.

[Syk]

You can just use your "This Year" measure to calculate the previous year as well. Try this

Bulletin (LY) =
CALCULATE ( [Bulletin (TY)], DATEADD ( 'Date Table'[date], -1, YEAR ) )

[rlupe]

I got the same results as the screenshot, it is probably due to when I select the Order Creation Year drilldown. I think by drilling down it holds back the previous year data because they are two different date columns, not sure. I have a date table, then on a different table there is create date and billing date. I could be getting them crossed up.

[Syk]

Try to remove filters on your order creation year and see if that helps.. Something like 

Bulletin (LY) =
CALCULATE (
    [Bulletin (TY)],
    DATEADD (
        'Date Table'[date],
        -1,
        YEAR
    ),
    REMOVEFILTERS ( 'Some Table'[Creation Year] )
)

[rlupe]

That is very close! It is technically applying it to the correct month/year, but I am trying to get January 2021 on the same line as January 2022.

I am trying to incorporate sametimeperiod last year to bring it down to the same year. Is there a way to re-add the year after the calculation is already done? I tried doing DATEADD in another measure but it did not hold the values from prev calculation.