Forum Discussion

SK87's avatar
SK87
Helper III
4 years ago
Solved

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 >=...
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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-9ad6a84cf5f2

SK87's avatar
SK87
Helper III
4 years ago

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

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    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