Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I got the following dax formula from another forum post.
This formula is ment to do the following:
When selecting year it should give results of the past year.
When selecting quarter (+year) it should give the results from previous quarter.
When selecting month (+year and quarter) it should give the results back from previous month.
The code I use:
Sales Amount Previous Period =
VAR DateCount =
COUNTROWS ( 'Date_T' )
VAR PeriodType =
SWITCH (
TRUE ();
AND (
HASONEVALUE ( Date_T[Year Number] );
DateCount = COUNTROWS ( PARALLELPERIOD ( Date_T[Date]; 0; YEAR ) )
); "year";
AND (
HASONEVALUE ( Date_T[Quarter Number] );
DateCount = COUNTROWS ( PARALLELPERIOD ( Date_T[Date]; 0; QUARTER ) )
); "quarter";
AND (
HASONEVALUE ( Date_T[Year Month Number]);
DateCount = COUNTROWS ( PARALLELPERIOD ( Date_T[Date]; 0; MONTH ) )
); "month";
AND (
HASONEVALUE ( Date_T[Year Number] );
DateCount = COUNTROWS ( DATESYTD ( Date_T[Date] ) )
); "year";
AND (
HASONEVALUE ( Date_T[Year Quarter Number] );
DateCount = COUNTROWS ( DATESQTD ( Date_T[Date] ) )
); "quarter"
)
RETURN
SWITCH (
PeriodType;
"year"; CALCULATE ( [Net Sales Amount]; PREVIOUSYEAR ( Date_T[Date] ) );
"quarter"; CALCULATE ( [Net Sales Amount]; PREVIOUSQUARTER ( Date_T[Date] ) );
"month"; CALCULATE ( [Net Sales Amount]; PREVIOUSMONTH ( Date_T[Date] ) )
)
However, this formula doesn't give any the results back for Year To Date.
It gives the total from previous year.
My questions: how can I use this formula but make it YTD?
Thanks guys in advance!!!
HI @Anonymous,
Switch function not suitable for calculate summarize records. when you use it on subtotal level, it not split records to each correspond level for calculate but only trigger on first case condition you defined.
In addition, HASONEVALUE function will return false when you calculate with multiple records.(it obviously need to deal with summarize records and subtotal level when you do YTD calculation)
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |