Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
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!
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 )
)
)
)
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |