Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic chart based on visual table selection, but chart shows expanded dates

To simplify, I have data set ID:   ID info 1 A 2 B 3 C 4 D 5 E   and data set X Date ID 1/1/2020 1 1/1/2020 3 1/2/2...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    You can create another dim_date table:

     

    Dimm_Date = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

     

    Then create one to many relationship between it and fact table.

     

    You can use the following measure to calculate history last week:

    historythisweek = CALCULATE(COUNT(Table2[ID]),FILTER(ALL(Dimm_Date),Dimm_Date[Date] >=MAX(Table2[Date])-7&&Dimm_Date[Date]<=MAX(Table2[Date])),ALLEXCEPT(Table2,Table2[ID]))

     

    Please refer to the pbix file.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

 

You can create another dim_date table:

 

Dimm_Date = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

 

Then create one to many relationship between it and fact table.

 

You can use the following measure to calculate history last week:

historythisweek = CALCULATE(COUNT(Table2[ID]),FILTER(ALL(Dimm_Date),Dimm_Date[Date] >=MAX(Table2[Date])-7&&Dimm_Date[Date]<=MAX(Table2[Date])),ALLEXCEPT(Table2,Table2[ID]))

 

Please refer to the pbix file.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai