Forum Discussion

auxilio99357's avatar
auxilio99357
Frequent Visitor
5 years ago

Help with DAX formula

Hi!!    I'm working on a sales report, where I have two tables a dimensional time (DimDates) table and a another one (Liquidaciones) that contains sales, date, amount and client. The thing is I wil...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi auxilio99357 

 

YEAR(NOW()) = YEAR(NOW()) -2 won't be true..what's the period you want, say it is Feb 20,2021, you want Jan 1 2020 to Dec 30 2020 or Jan 1 2020 to Today?

 

 

auxilio99357's avatar
auxilio99357
Frequent Visitor
5 years ago

Hi @Vera_33 

 

Yes you are right, what i wanted to compare in the IF's logical test was if the current year is the same as the one two months ago. For example if today was the 10/01/2020, the logical test is true (cause the year of 08/01/2020 is the same) but if we where on the 01/01/2020 it wont be true (cause the year of 11/01/2019 is not the same).

 

If we are on Feb 20, 2021 the period I want to calculate is from jan 1,2019 to dec 30, 2019

If we are on Oct 20,2021 the period I want to calculate is Jan 1, 2020 to Ago 20,2020

 

I want to calculate the LYTD but as if today was two month from now

Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi auxilio99357 

     

    I am still a little bit confused.

    So it is Feb 23, 2021, you want Jan 1 2020 to Dec 31 2020,

    if it is Jan 23, 2021, you want the same as above or Jan 1 2020 to Nov 30 2020?

    if it is Oct 23, 2021, you want Jan 1 2021 to Aug 31 2021, right?

    And you hard coded NOW()? Or you have other filter/slicer to specify a date?

    Below is one returning Jan 23 = Feb 23

     

    LYTD2 =
VAR CurMonth =
    MONTH ( NOW () )
VAR CurYear =
    YEAR ( NOW () )
RETURN
    IF (
        CurMonth IN { 1, 2 },
        CALCULATE (
            SUM ( Liquidaciones[U_Liq] ),
            FILTER ( VALUES ( DimDates[Date] ), DimDates[Year] = CurYear - 1 )
        ),
        CALCULATE (
            SUM ( Liquidaciones[U_Liq] ),
            FILTER (
                VALUES ( DimDates[Date] ),
                DimDates >= DATE ( CurYear, 1, 1 )
                    && DimDates
                        < DATE ( CurYear, CurMonth - 1, 1 )
            )
        )
    )