Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
auxilio99357
Frequent Visitor

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:
 
Capture2.JPG
Can you help me with the measure?
 
Thank you!
Regards
 
 
 
 

 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Vera_33
Resident Rockstar
Resident Rockstar

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?

 

 

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!

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.