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 will always have the amount of sales up to two months before the current day. (For example now that we are on February 2021, the last sales I have are from December 2020).

 

To model the Data I already use a lot of time intelligent measures but I didn't manage to make the measure LYTD (last year to date) work correctly because of the two month difference between the current day and the last period of sales. 

 

To make the report calculate LYTD automatically I create the following measure but it doesnt work:

 

LYTD2 = IF(YEAR(NOW()) = YEAR(NOW()) -2,
CALCULATE(SUM(Liquidaciones[U_Liq]), DATESBETWEEN(DimDates[Date],DATE(YEAR(NOW())-1, 1, 1), DATE(YEAR(NOW()) -1, month(NOW()) -2, 30))),
CALCULATE(SUM(Liquidaciones[U_Liq]), DATESBETWEEN(DimDates[Date],DATE(YEAR(NOW())-2, 1, 1), DATE(YEAR(NOW()) -2, month(NOW()) -2, 30))))
 
I´m showing the measures in a matrix table like this:
 
Can you help me with the measure?
 
Thank you!
Regards
 
 
 
 

 

Need Help

4 Replies

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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI auxilio99357,

    Did Vera_33's suggestion help with your scenario? If that is the case, you can kudo or accept her suggestion to help others who faced similar issues to find this more quickly.

    If not, can please share some dummy data with similar table schema and expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng