Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Slicers on calculated columns

Hi all,   I'm feeling a particular frustration with one of my visualisations.   I have a data table which looks something like   Date Type Count 01/09/2020 A 1 01/09/2020 B 2 ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    Would you please try to use the following measure:

     

     

    Measure =
VAR A =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Plant Forecasts'[Selection_Count] ),
        ALLSELECTED ( 'Plant Forecasts'[Plant Type] ),
        'Plant Forecasts'[StartDate] <= A,
        'Plant Forecasts'[EndDate] > A,
        'Plant Forecasts'[StageCode_Name] <> "Won"
    )

     

     

     

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dedmon,

 

I'm happy to provide an example but the usual dropbox/file sharing options aren't available to me as my company blocks them.

 

Essentially this is counting equipment hires so a hire may start on 1st September and be out for say two weeks. This would mean that I would need the chart to show 1 item for 14 days as opposed to now where it shows the one item for the 1 day. I have multiple hires starting on different days and running for different periods. The calculate function that allows me to do that looks like

 

= calculate(sum('Plant Forecasts'[Selection_Count]),filter('Plant Forecasts','Plant Forecasts'[StartDate]<='Date'[Date]),filter('Plant Forecasts','Plant Forecasts'[EndDate]>'Date'[Date]),filter('Plant Forecasts','Plant Forecasts'[StageCode_Name]<>"Won"))
 
Using a measure forces me to apply a MAX to all of my dates meaning I only ever see the counts on a date that a hire starts, not the ongoing count of it between two dates.
v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

Would you please try to use the following measure:

 

 

Measure =
VAR A =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Plant Forecasts'[Selection_Count] ),
        ALLSELECTED ( 'Plant Forecasts'[Plant Type] ),
        'Plant Forecasts'[StartDate] <= A,
        'Plant Forecasts'[EndDate] > A,
        'Plant Forecasts'[StageCode_Name] <> "Won"
    )

 

 

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    That seems to have cracked it. Awesome, thank you!