Forum Discussion

rlupe's avatar
rlupe
Frequent Visitor
3 years ago

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

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: 

Need Help

6 Replies

  • Syk's avatar
    Syk
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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's avatar
      rlupe
      Frequent Visitor
      3 years ago

      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's avatar
        Syk
        Icon for Resident Rockstar rankResident Rockstar
        3 years ago

        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's avatar
      rlupe
      Frequent Visitor
      3 years ago

      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.