Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I've added many columns to the calendar using the DAX via "New Column". I can't figure out how to make a column with past dates (ending yesterday) to simplify YTD, PRIOR YTD measures. The reports have month and year filters on them and that column would allow PREVIOUSYEAR() to be used for prior year. PREVIOUSYEAR() returns the entire year due to the calendar having future dates.
I made a column BI_Calendar[Date_Sales] that contains TRUE the formula being:
Thank you in advance. I GIVE KUDOS and mark solved when solved!
Solved! Go to Solution.
Hi @Anonymous - don't truncate your date table. See this article by SQLBI on hiding future dates in measures. It involves a very simple "Is Future" type column to filter your date table on. The crux of it is a measure like this:
Sales YTD hide v1 =
CALCULATE (
[Sales Amount],
CALCULATETABLE (
DATESYTD ( 'Date'[Date] ),
'Date'[DatesWithSales] = TRUE
)
)
You would include that CALCULATETABLE() in your CALCULATE() function, then it couldn't see any future dates. Note that your model must refresh daily or the IsFuture column will get stale and report incorrect results until refreshed again.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous - don't truncate your date table. See this article by SQLBI on hiding future dates in measures. It involves a very simple "Is Future" type column to filter your date table on. The crux of it is a measure like this:
Sales YTD hide v1 =
CALCULATE (
[Sales Amount],
CALCULATETABLE (
DATESYTD ( 'Date'[Date] ),
'Date'[DatesWithSales] = TRUE
)
)
You would include that CALCULATETABLE() in your CALCULATE() function, then it couldn't see any future dates. Note that your model must refresh daily or the IsFuture column will get stale and report incorrect results until refreshed again.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans ,
I keep running into problems due to the many different look back periods and requests for Month slicers.
The last place I worked they had a Microsoft team build out the cubes and they had a date field with only past dates (closed accounting dates) and that was the field used by everyone for reports.
The newest problem that could be solved with a new date column of only past dates is accounting: Today is October 1 (22 work days), we are reporting September (21 work days).
If I have a column of only past dates the month would still be September. I tried to compensate with the below but it doesn't work:
Thank you! KUDOS!
Glad to assist @Anonymous
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you except, I can get YTD. I need Prior YTD.
SAMEPERIODLASTYEUR() doesn't ignore the time slicer in the report
PREVIOUSYEAR() returns the total prior year. I tried filtering it but that didn't work (here is that fail)