Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Different Time Columns in Matrix

I have a Calendar Table and this is linked to a payment Table via Payment Date. My Calendar Table looks like this:

 

Calendar = 
ADDCOLUMNS (
CALENDAR (DATE(2008,1,1), DATE(2022,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"WeekNum", WEEKNUM([Date])
)

 

I have a Matrix table that shows various sums of amounts (By a Source and by a Product Type) - What I need is for the Columns to show this:

 

Week | Month | Year

Where Week is the current Week and Month is the Current Month and Year is the Current year

 

if I select the WeekNum from my Calendar for the Columns - it's every WeekNum, If I add in a filter for that WeekNum - It applies to all dates.

 

I'm sure I'm missing the obvious.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

     

    if you want to select this week as slcier filter, you need to have columns like

     

    Start Week = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1


    End date= 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

     

    Week Type = Switch( True(),
    [start week]<=Today() && [end date]>=Today(),"This Week" ,
    [start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
    [Week Name]
    )

     

    and select this week

    refer: https://www.youtube.com/watch?v=hfn05preQYA

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for that - I've added the additional columns in my Date Table - which is helpful - having a dynamic measure of 'This Week', 'This Month' and 'This Year' is super helpful.
      Ideally I'd like this to show in a Matrix as a column - I've worked around this by just creating multiple matrixes:

       

      See the below Images (I've blacked out all sensitive info) to give an idea of what I'm trying to achieve,This is the desired formatThis is what I currently have