Showing Measure Value only the First Date & Last Date of the Date Slicer Power Bi

[baijumohan1990]

I am working on a Power BI Report (which is in direct query mode with SSAS tabular model as source). I need to create two measures opening base and closing base with below condition.

opening base should be show value only for the minimum date range chosen by the user in the date slicer.

closing base should be show value only for the maximum date range chosen by the user in the date slicer.

For e.g : if user chooses between may 1, 2020 and may 17,2020 opening base will show value only for may 1, 2020 & closing base show value only for may 17th,2020. For other dates value should be 0. I have tried below

Created a measure to get the minimum date of the date slicer.

StartDate = CALCULATE(min('DATE'[DATE]),ALLSELECTED('DATE'[DATE])) 

used this measure as filter in the opening base measure calculation.

openingbase = CALCULATE(sum(MEASUREMENTS[OPENING_BASE]),FILTER('DATE','DATE'[DATE]=[StartDate]))

But this is not working as I am getting opening base value for all the date ranges selected. Any help much appreciated.

[Ashish_Mathur]

Hi,

Does this measure work?

openingbase=Calculate(sum(MEASUREMENTS[OPENING_BASE]),datesbetween('DATE'[DATE],min('DATE'[DATE]),min('DATE'[DATE])))

Hope this helps

[baijumohan1990]

Hi Ashish,

Thanks for you reply. Its working partially . Now opening base is calcuated only for the mininum date but its replicating across all the date ranges choosed as shown in the below screenshot. Could you please advice?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-deddai1-msft]

Hi @baijumohan1990 ,

Would you please try the following measure:

openingbase =
CALCULATE (
    SUM ( 'MEASUREMENTS'[OPENING_BASE] ),
    FILTER ( ALL ( 'MEASUREMENTS' ), 'MEASUREMENTS'[DATE] = MIN ( 'DATE'[DATE] ) )
)

Best Regards,

Dedmon Dai

[baijumohan1990]

hi ,

I tried it didnt' worked out..

[v-deddai1-msft]

Hi @baijumohan1990 ,

In my test data ,  it works well.  Would  you please show us some sample data for test?

Best Regards,

Dedmon Dai