Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I have a visual displaying the days of the month (1 march, 2 march, 3 march, without the year) showing the sales of the day of the current year and the sales of the day of the previous year :
2023 2022
1 March 10 8
2 March 12 10
3 March 9
4 March 7
Let's say today is 2 March 2023 and I would like the figures of the previous year showed only until 2 March 2022.
I tried the following code but it keeps showing data until 4 March for the previous year like above :
Any idea?
Thank you
Raphaël
Solved! Go to Solution.
Thank you. It stops now at the right date but figures are not correct, all much too high (but not all the same).
This returns the good numbers but don't stop at the right date :
Do you know why
Would you please explain again what exactly the value are you exyto see for each date?
@tamerj1 for each day of the month (1,2,3...) I would like to have the sales of the year and the sales of the previous year. This works simply with the measure
Hi @Raph
Please try
Actual previous year =
IF (
MAX ( 'Date'[Date] ) <= CALCULATE ( MAX ( 'Sales'[Date] ), ALL ( 'Date' ) ),
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Day] ) )
)
Thank you very much, @tamerj1 , this is indeed working well except that I lost the sum for the current month (sum is normally displayed if we filter on a a previous month)
you mean total?
Actual previous year =
SUMX (
VALUES ( 'Date'[Day-Month] ),
IF (
CALCULATE ( MAX ( 'Date'[Date] ) )
<= CALCULATE ( MAX ( 'Sales'[Date] ), ALL ( 'Date' ) ),
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Day] ) )
)
)
Thank you, yes I meant the Total... I tried with SUMX (first on my own, iterating the days of Date table and then with your suggestion) but the visual won't change...
Hi @tamerj1
Thank you very much for you answered which looked smart to me.
I tried it but now I have a big number, always the same, repeating on all the rows of the month... typically like a data modeling problem. The model is really very simple : 2 dim tables (date and stores) linked to 2 fact tables (sales and budget).
This worked well for the budget :
@Raph
Seems I misunderstood the requirement. Please try
Actual previous year =
VAR CurrentDate =
MAX ( 'Date'[Date] )
VAR Dates =
FILTER (
PREVIOUSYEAR ( 'Date'[Date] ),
FORMAT ( 'Date'[Date], "MMDD" ) <= FORMAT ( CurrentDate, "MMDD" )
)
RETURN
CALCULATE ( [Total Sales], Dates )
Hi @Raph
Please try
Actual previous year =
VAR LastSalesDate =
CALCULATE ( MAX ( 'Sales'[Date] ), REMOVEFILTERS () )
VAR Dates =
FILTER (
PREVIOUSYEAR ( 'Date'[Date] ),
FORMAT ( 'Date'[Date], "MMDD" ) <= FORMAT ( LastSalesDate, "MMDD" )
)
RETURN
CALCULATE ( [Total Sales], Dates )
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |