Forum Discussion

HenryJS's avatar
HenryJS
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

New Table: Columns from Dates

Hi all,

 

I need to create a new table.

 

I have table 1 (below). This would then be used via a measure/calculation to create the table 2 below.

 

i.e. the dates specified in table 1 would create a "yes" if the week column in table 2 falls within this date range.

 

Is this possible?

 

 

1. Original table with dates

 

TempnameSTART.DATEEND.DATE
Tran Peter04/11/201926/04/2020
Lewis Michael09/03/202028/02/2021
Konior Beata21/10/201929/03/2020
Jarrous Nicholas29/02/202022/06/2020
Whitehead Thomas16/07/201926/04/2020
Reddy Anthony04/11/201926/04/2020
Hernandez Andres10/02/202002/07/2020
Abroud Mehdi29/07/201931/07/2020
Turner Joseph17/06/201905/04/2020

 

 

2. Table required

 

TempnameWeek 40Week 41Week 42Week 43Week 44Week 45Week 46Week 47Week 48Week 49
Tran PeterYesYesYesYesYesYesYesYesYesYes
Lewis Michael          
Konior BeataYesYesYesYesYesYesYesYesYesYes
Jarrous Nicholas        YesYes
Whitehead ThomasYesYesYesYesYesYesYesYesYesYes
Reddy AnthonyYesYesYesYesYesYesYesYesYesYes
Hernandez Andres      YesYesYesYes
Abroud MehdiYesYesYesYesYesYesYesYesYesYes
Turner JosephYesYesYesYesYesYesYesYesYesYes
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    6 years ago

    Hi HenryJS .

     

    Add a calendar table with a WeekNumber column then add the following measure to your model:

    Active=
IF (
    SELECTEDVALUE ( 'Table'[START.DATE] ) <= MAX ( 'Calendar'[Date] )
        && SELECTEDVALUE ( 'Table'[END.DATE] ) >= MAX ( 'Calendar'[Date] );
    "Yes";
    ""
)

    Now use the Date column on your matrix column and the names on the lines, use the measure created as values.

     

    Check PBIX file attach.

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi HenryJS .

     

    Add a calendar table with a WeekNumber column then add the following measure to your model:

    Active=
IF (
    SELECTEDVALUE ( 'Table'[START.DATE] ) <= MAX ( 'Calendar'[Date] )
        && SELECTEDVALUE ( 'Table'[END.DATE] ) >= MAX ( 'Calendar'[Date] );
    "Yes";
    ""
)

    Now use the Date column on your matrix column and the names on the lines, use the measure created as values.

     

    Check PBIX file attach.

    • HenryJS's avatar
      HenryJS
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      MFelix thank you, is it possible to change the week numbers to the format WC. 

       

      So instead of 2020-01 it would be 01/01/20

       

      Thanks,

       

      Henry

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        HI HenryJS ,

         

        I just did a simple format of the week, you can changed it to any format you need, the only question the is needed is that the days in that week column all have the same data so it can be comparable.

         

        Assuming WC format is the first day of that week? (Sorry for the question but not used to this formating syntax of WC)

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    HenryJS ,

    Try these measures with a date table having a week. I am sharing a link one example file for the date table

     

    Measure =
    if(calculate(count(Date[Date]),filter(Table,Table[start_date]<=max(Date[Week Start]) && Table[end_date]>=max(Date[Week Start])))>0, "Yes","No")


    Measure =
    if(calculate(count(Date[Date]),filter(Table,Table[start_date]<=max(Date[Date]) && Table[end_date]>=max(Date[Date])))>0, "Yes","No")

     

    refer Date table in https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0