Forum Discussion

mithiladas02's avatar
mithiladas02
Icon for Helper I rankHelper I
5 years ago

Colum Header time period matrix

My matrix table in power bi is just like that. Every tuesday i have to refresh the dataset. the weekday is saturday to friday. how do i update my date when i update the data automatically in column header? For expample, on 8/31(tuesday) when i refresh the dataset in my column header it will show for prior week on 8/21, Trans in-/21 to 8/27,  Current week- 8/27.

 

GroupPrior week  on 8/14Trans in 8/14 to 8/20Trans out 8/14 to 8/20Current week 8/20
a1102
b2002
c3003

3 Replies

  • mithiladas02 , For Saturday week you can get

    2.Any Weekday Week - Start From Any day of Week
    https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

     

    Using the start Week snd end week create

     

    Week Type = Switch( True(),
    [start week]<=Today() && [end date]>=Today(),"Current Week" & format([start week], "mm/dd") ,
    [start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" & format([start week], "mm/dd") ,
    [Week Name]
    )

  • thanks for you reply. 

     

    I wrote the sytex as below

    var pw= 'Date'[Date]+-1*if(WEEKDAY('Date'[Date])<7,WEEKDAY('Date'[Date]),WEEKDAY('Date'[Date])-7)

    VAR PWNAME='Date'[Date]+-1*if(WEEKDAY('Date'[Date])<7,WEEKDAY('Date'[Date]),WEEKDAY('Date'[Date])-7),"MM/DD")

    var transin=[Start date] & " to "& [ End date]

    VAR TRANSINNAME=[Week Start date] & " to "& [Week End date],"mm/dd"

    var cw='Date'[Week Start date(Sat-Fri)]+6

    var cwname='Date'[Week Start date(Sat-Fri)]+6,"mm/dd

    var result=
    pw,"prior week"& "(" & pwname & ")",
    cw,"current week" & "(" & cwname & ")",
    transin, "transin" & "(" transinname & ")"
    )

    return result

     

     

     

    but it is showing me the -"switch function  does not support comparing value of types true/ false with value of type Data , Consider using the value or format function to convert one of the values.

     

    what i am doing wrong? will you please help me?