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.
Solved! Go to Solution.
After my research, you can do these follow my steps as below:
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)
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" )
by the way, when adding calculate table it can’t be affected by slicers.
And this link may give you some ideas.