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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How do I add a calendar column that will have only past dates?

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:

Date_Sales = 'BI_Calendar'[Date] <= MAX(FACT_Sales[CalendarKey] ) 
 
I couldn't figure out how to use that to get previousYTD. Maybe that can be used to make a new calendar column.

 

Thank you in advance. I GIVE KUDOS and mark solved when solved!

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

@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:

working_days =
CALCULATE ( COUNTROWS ( BI_Calendar ),
FILTER ( BI_Calendar,
AND( BI_Calendar[Month] = MONTH( TODAY () -1 ) ,
not WEEKDAY( BI_Calendar[Day of Week]) in {1,7}
) ))
 
Can you help with creating a column of past dates please and thank you with KUDOS!
Anonymous
Not applicable

Thank you! KUDOS! 

 

Glad to assist @Anonymous 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

Sales_USD_YTDPrior =
CALCULATE(FACT_Sales[Sales_USD_Net_Dis],
PREVIOUSYEAR( BI_Calendar[Date]),
BI_Calendar[Date_Sales] = TRUE ) ( I was hoping this filtered the current year but it did not)
 
The column BI_Calendar[Date_Sales] I made and it is TRUE for a date with a sale. But, all the prior year is TRUE. 
 
I thought simply adding a column with only past dates (Closed_Dates) was the easiest and cleanest. 

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors