Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
SK87
Helper III
Helper III

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 DateEnd DateCOUNT
12/30/20171/4/20181
12/28/20171/18/20181
12/16/20171/16/20181
12/6/20172/1/20181
12/4/20171/16/20181
12/21/20172/27/20181
12/27/20172/26/20181
12/20/20162/15/20181
12/20/20172/5/20181
12/30/20172/23/20181
12/20/20171/19/20181
12/15/20171/21/20181
12/15/20171/21/20181
9/14/20161/26/20181
12/21/20172/2/20181
12/28/20172/20/20181
12/2/20171/31/20181
12/11/20172/27/20181
11/21/20162/23/20181
12/7/20172/28/20181
12/27/20172/2/20181
12/11/20172/13/20181
12/29/20172/2/20181
12/4/20171/19/20181
11/16/20162/12/20181
12/14/20171/11/20181
12/15/20172/22/20181
12/27/20171/13/20181
12/18/20172/25/20181
12/27/20171/12/20181
12/10/20171/12/20181
12/1/20171/22/20181
12/7/20172/23/20181
12/17/20162/15/20181
12/23/20172/10/20181
12/22/20172/2/20181
12/27/20172/26/20181
12/27/20172/26/20181
12/27/20171/18/20181
12/23/20171/31/20181
12/22/20171/9/20181
12/2/20171/9/20181
12/21/20171/9/20181
12/18/20171/24/20181
12/14/20172/8/20181
12/14/20172/13/20181
12/5/20172/18/20181
12/5/20172/18/20181
12/21/20171/31/20181
12/19/20172/28/20181
11/10/201612/30/20501
12/27/20172/15/20181
12/27/20172/15/20181
12/8/20172/1/20181
12/16/20172/14/20181
12/30/20172/28/20181
12/14/20172/28/20181
12/15/20171/26/20181
12/1/20172/18/20181
12/11/20172/26/20181
12/1/20172/16/20181
12/1/20171/20/20181
12/22/20171/13/20181
10/7/20162/2/20181
12/4/20172/28/20181
12/6/20172/3/20181
12/12/201712/30/20501
12/12/201712/30/20501
12/13/20172/10/20181
10/24/20161/14/20181
9/14/20162/17/20181
9/14/20162/17/20181
12/20/20161/27/20181
12/5/20172/20/20181
10/28/20162/28/20181
12/14/20172/21/20181
12/1/20172/9/20181
12/13/20172/13/20181
12/4/20172/18/20181
12/4/20172/18/20181
12/19/20162/6/20181
12/6/20172/21/20181
12/18/20172/27/20181
12/29/20162/28/20181
12/4/20172/9/20181
12/4/20171/28/20181
12/5/20171/25/20181
11/16/20161/16/20181
12/19/20161/30/20181
9/23/20162/15/20181
12/19/20172/5/20181
12/9/20162/17/20181
12/12/20172/15/20181
9/12/20162/10/20181

Thanks in advance, Kindly suggest.

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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-o...
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks @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

SK87
Helper III
Helper III

@tamerj1 Can you help on above problem

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.