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
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 )
            )
        )
    )