cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CJH Regular Visitor
Regular Visitor

Need a Date Table that filters current YTD and Prior YTD

Hello,

 

We have OLAP cubes and I would like to create a date table that dynamically filters current and prior YTD dates only.   I do not need to calculate or sum anything.  Once I get my relevant dates filtering, I can create a hierarchy in power bi that will display my summed data for a profit and loss statement.  

 

I'm thinking to create my table I need to incorporate a dateadd filter.   The only dates I need in the date table would be date, month, month number, quarter and year.

 

Any help would be very appreciated.

 

Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions
CJH Regular Visitor
Regular Visitor

Re: Need a Date Table that filters current YTD and Prior YTD

Thank you Lin!!  That worked beautifully.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Need a Date Table that filters current YTD and Prior YTD

hi,@CJH

     After my research, you can do these follow my steps as below:

Step1:

Create a CALENDAR date first.

Then use this formula to create the date table that contains the current year and last year

Date = CALCULATETABLE('CALENDAR',YEAR('CALENDAR'[Date])<=YEAR(TODAY())&&YEAR('CALENDAR'[Date])>=YEAR(TODAY())-1)

Step 2
use these formulas to add column month, month number, quarter and year.

Year = YEAR('Date'[Date])

Quarter = INT ( FORMAT ( [Date], "q") ) 

Month number = MONTH ( 'Date'[Date] ) 

Month = FORMAT ( 'Date'[Date], "mmmm" ) 

Result:

8.PNG

 

by the way, when adding calculate table it cant be affected by slicers.

And this link may give you some ideas.

https://community.powerbi.com/t5/Desktop/Create-dynamic-DAX-table-filtered-by-slicer-from-another-ta...

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CJH Regular Visitor
Regular Visitor

Re: Need a Date Table that filters current YTD and Prior YTD

Thank you Lin!!  That worked beautifully.