3 Weeks Ago Last Year

[Anonymous]

Hi,

I am trying to create a formula that gives me dates from my dataset from 3 weeks ago last year.

My formula is: 

3 weeks agoLast Year = if(or(OR(OR(OR(OR(OR( Initiated[INIT DATE]=DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW ()-14 ) ), Initiated[INIT DATE]=DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () -15))),Initiated[INIT DATE]=DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () -16))), Initiated[INIT DATE]=DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () -17))),Initiated[INIT DATE]=DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () -18))),Initiated[INIT DATE]=DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () -19))), Initiated[INIT DATE]=DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () -20))),1,0)

This formula worked when trying to find last weeks data last year, but when trying to find 3 weeks ago, instead of giving me 1/31/2019, it gives me 3/1/2019?

I am not sure why...

Thank you!
Sarah

[Greg_Deckler]

I think that you would have better luck with Sequential. https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231?search-action-id=10198...

[Anonymous]

I dont think so because my calculation takes into account todays date and the previous weeks from todays date. If I assign week numbers, and todays date changes from outside of the assigned week number, it will not do what I want it to do 

[Anonymous]

THis formula

3 weeks agoLast Year =
IF (
    OR (
        OR (
            OR (
                OR (
                    OR (
                        OR (
                            Initiated[INIT DATE]
                                = DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () - 14 ) ),
                            Initiated[INIT DATE]
                                = DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () - 15 ) )
                        ),
                        Initiated[INIT DATE]
                            = DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () - 16 ) )
                    ),
                    Initiated[INIT DATE]
                        = DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () - 17 ) )
                ),
                Initiated[INIT DATE]
                    = DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () - 18 ) )
            ),
            Initiated[INIT DATE]
                = DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () - 19 ) )
        ),
        Initiated[INIT DATE]
            = DATE ( YEAR ( NOW () ) - 1, MONTH ( NOW () ), DAY ( NOW () - 20 ) )
    ),
    1,
    0
)

Returns 1 if the Initiated Date is last year, same month, but day is -14 to -20.

It does not return last 3 weeks of last year, since the month is the same. So if today is 5 of February, 3 weeks ago (or -21 days ago) is around 16 of January, but your formula uses the same month. And DAY(NOW()-15) will be a nonsense number as it will be -10

Do you have a date dimension? If so this is a time intelligence metrics, that can be caclulate using CALCULATE and proper filter modification.
 

[Anonymous]

Ahh makes sense.... I am new to Power BI, I am not sure about a date dimension, but i am just trying to sum up all dates from a week, 3 weeks ago 

is this possible with a measure calculation?

Thank you!
Sarah

[Anonymous]

Exactly, "3 weeks ago last year" what does it mean?

So today it's Feb 18 - tuesday, 2020. What is the date range you want to calculate?

Do I understand correctly that you want to calculate last 3 weeks for this year (so, technically, last 21 days) so it's from 28th of Jan to yesterday and THEN use the same date range for 2019?

[Anonymous · Accepted solution]

So 3 weeks ago last year means Jan 28 2019-Feb 4 2019

I have a calculation finding the total from 3 weeks ago THIS year, but I am trying to compare that to last year... 

I actually found out a way to do this... i just subtract 372 days from my date.... haha

[Greg_Deckler]

Does that work for leap years and non leap years? I have a feeling once you get to Feb 29 you might have an issue.

[Anonymous]

as @Greg_Deckler said, your solution works but you have to be careful with leap years. 

[Anonymous]

Also, generally speaking you always want to have a date dim when working with date times. Here's an example

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

[amitchandak]

Assume you want same Monday, year + 3 weeks behind then it 55 weeks behind

55 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-55*7,Day))

If you need exactly a year and 3 weeks behind

55 behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],365+12,Day))
//Below is not tested 
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin