Forum Discussion
Calculated Column based on two tables
Hi Team,
I am facing one problem that is I have two dates "start date" and "End Date". I need to get the count as per below scenario:
If I select values <= December'2017 in start date and values >=Dec'2017 in end date. the count should be 94 based on the below excel table. (That switch column will return 0s or 1s depending on the value selected by the User)
Problem I need to create a calculated table based on Calendar Date (which is another table) on Raw data set
Challenge: A value that user selected is considered dynamic in nature and can't be treated as a global variable across any tables that I have constructed in PBI.
| Start Date | End Date | COUNT |
| 12/30/2017 | 1/4/2018 | 1 |
| 12/28/2017 | 1/18/2018 | 1 |
| 12/16/2017 | 1/16/2018 | 1 |
| 12/6/2017 | 2/1/2018 | 1 |
| 12/4/2017 | 1/16/2018 | 1 |
| 12/21/2017 | 2/27/2018 | 1 |
| 12/27/2017 | 2/26/2018 | 1 |
| 12/20/2016 | 2/15/2018 | 1 |
| 12/20/2017 | 2/5/2018 | 1 |
| 12/30/2017 | 2/23/2018 | 1 |
| 12/20/2017 | 1/19/2018 | 1 |
| 12/15/2017 | 1/21/2018 | 1 |
| 12/15/2017 | 1/21/2018 | 1 |
| 9/14/2016 | 1/26/2018 | 1 |
| 12/21/2017 | 2/2/2018 | 1 |
| 12/28/2017 | 2/20/2018 | 1 |
| 12/2/2017 | 1/31/2018 | 1 |
| 12/11/2017 | 2/27/2018 | 1 |
| 11/21/2016 | 2/23/2018 | 1 |
| 12/7/2017 | 2/28/2018 | 1 |
| 12/27/2017 | 2/2/2018 | 1 |
| 12/11/2017 | 2/13/2018 | 1 |
| 12/29/2017 | 2/2/2018 | 1 |
| 12/4/2017 | 1/19/2018 | 1 |
| 11/16/2016 | 2/12/2018 | 1 |
| 12/14/2017 | 1/11/2018 | 1 |
| 12/15/2017 | 2/22/2018 | 1 |
| 12/27/2017 | 1/13/2018 | 1 |
| 12/18/2017 | 2/25/2018 | 1 |
| 12/27/2017 | 1/12/2018 | 1 |
| 12/10/2017 | 1/12/2018 | 1 |
| 12/1/2017 | 1/22/2018 | 1 |
| 12/7/2017 | 2/23/2018 | 1 |
| 12/17/2016 | 2/15/2018 | 1 |
| 12/23/2017 | 2/10/2018 | 1 |
| 12/22/2017 | 2/2/2018 | 1 |
| 12/27/2017 | 2/26/2018 | 1 |
| 12/27/2017 | 2/26/2018 | 1 |
| 12/27/2017 | 1/18/2018 | 1 |
| 12/23/2017 | 1/31/2018 | 1 |
| 12/22/2017 | 1/9/2018 | 1 |
| 12/2/2017 | 1/9/2018 | 1 |
| 12/21/2017 | 1/9/2018 | 1 |
| 12/18/2017 | 1/24/2018 | 1 |
| 12/14/2017 | 2/8/2018 | 1 |
| 12/14/2017 | 2/13/2018 | 1 |
| 12/5/2017 | 2/18/2018 | 1 |
| 12/5/2017 | 2/18/2018 | 1 |
| 12/21/2017 | 1/31/2018 | 1 |
| 12/19/2017 | 2/28/2018 | 1 |
| 11/10/2016 | 12/30/2050 | 1 |
| 12/27/2017 | 2/15/2018 | 1 |
| 12/27/2017 | 2/15/2018 | 1 |
| 12/8/2017 | 2/1/2018 | 1 |
| 12/16/2017 | 2/14/2018 | 1 |
| 12/30/2017 | 2/28/2018 | 1 |
| 12/14/2017 | 2/28/2018 | 1 |
| 12/15/2017 | 1/26/2018 | 1 |
| 12/1/2017 | 2/18/2018 | 1 |
| 12/11/2017 | 2/26/2018 | 1 |
| 12/1/2017 | 2/16/2018 | 1 |
| 12/1/2017 | 1/20/2018 | 1 |
| 12/22/2017 | 1/13/2018 | 1 |
| 10/7/2016 | 2/2/2018 | 1 |
| 12/4/2017 | 2/28/2018 | 1 |
| 12/6/2017 | 2/3/2018 | 1 |
| 12/12/2017 | 12/30/2050 | 1 |
| 12/12/2017 | 12/30/2050 | 1 |
| 12/13/2017 | 2/10/2018 | 1 |
| 10/24/2016 | 1/14/2018 | 1 |
| 9/14/2016 | 2/17/2018 | 1 |
| 9/14/2016 | 2/17/2018 | 1 |
| 12/20/2016 | 1/27/2018 | 1 |
| 12/5/2017 | 2/20/2018 | 1 |
| 10/28/2016 | 2/28/2018 | 1 |
| 12/14/2017 | 2/21/2018 | 1 |
| 12/1/2017 | 2/9/2018 | 1 |
| 12/13/2017 | 2/13/2018 | 1 |
| 12/4/2017 | 2/18/2018 | 1 |
| 12/4/2017 | 2/18/2018 | 1 |
| 12/19/2016 | 2/6/2018 | 1 |
| 12/6/2017 | 2/21/2018 | 1 |
| 12/18/2017 | 2/27/2018 | 1 |
| 12/29/2016 | 2/28/2018 | 1 |
| 12/4/2017 | 2/9/2018 | 1 |
| 12/4/2017 | 1/28/2018 | 1 |
| 12/5/2017 | 1/25/2018 | 1 |
| 11/16/2016 | 1/16/2018 | 1 |
| 12/19/2016 | 1/30/2018 | 1 |
| 9/23/2016 | 2/15/2018 | 1 |
| 12/19/2017 | 2/5/2018 | 1 |
| 12/9/2016 | 2/17/2018 | 1 |
| 12/12/2017 | 2/15/2018 | 1 |
| 9/12/2016 | 2/10/2018 | 1 |
Thanks in advance, Kindly suggest.
Hi， SK87
If you switch the variable date by slicer, then you should create a measure instead of a calculatted column to count the value. Calculated columns are unaffected by the value selected in the slicer.
You can create a measure like below and apply it to table visual.
Count = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Start Date] <= SELECTEDVALUE ( 'Calendar Date'[Date] ) && 'Table'[End Date] >= SELECTEDVALUE ( 'Calendar Date'[Date] ) ) )Best Regards,
Community Support Team _ Eason
4 Replies
SK87 , You can create an independent date table and create a measure like
//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Count]), filter('Table', 'Table'[Start Date] <=_max && 'Table'[End Date] >=_max ))refer
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2Thanks amitchandak
But I need to calculate Count column which you have mentioned in measure as Table[Count]. I had shared above example after calculating in excel but how I can get same count in PBI using date 1 independent variable
Hi， SK87
If you switch the variable date by slicer, then you should create a measure instead of a calculatted column to count the value. Calculated columns are unaffected by the value selected in the slicer.
You can create a measure like below and apply it to table visual.
Count = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Start Date] <= SELECTEDVALUE ( 'Calendar Date'[Date] ) && 'Table'[End Date] >= SELECTEDVALUE ( 'Calendar Date'[Date] ) ) )Best Regards,
Community Support Team _ Eason
