Forum Discussion

prashett7's avatar
prashett7
Frequent Visitor
2 years ago
Solved

Measure to Calculated Column

Here is the snap shot of the visual.

 

I have a slicer to select the start date and end date. I want to have similar slicer (Slider with min and max) for filtering the avg revenue per day. Also, slicer values need to change as per the date selection.

 

 

Currently the avg revenue per day comes from a measure. I'm storing the minimum date of the slicer as StrDate and maximum date as EndDate. 

avg revenue per day =
DIVIDE(
    SUM(Data[revenue]),
    [EndDate]-[StrDate],
    0
)
 
StrDate = MIN(Data[date]) 
EndDate = MAX(Data[date])
 
I cannot use a measure for a slicer. I tried creating a calculated column. However, calculated column is not able to pick the StrDate and EndDate as per user selection.
 

 

Is there any work around to achieve this? Any help on this is much appreciated.

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi prashett7 ,

    Sorry, there is no workaround.

    In Power BI, the slicer value must be a field value, but you need it change according to another date slicer, it could not be achieveable .

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • prashett7's avatar
    prashett7
    Frequent Visitor
    2 years ago

    Sample Data in case you need it.

     

    18-12-2021bengaluruElectronicsSmartphones₹ 51,500
    30-12-2021mumbaiElectronicsLaptop₹ 88,100
    05-01-2022udupiElectronicsHeadphones₹ 25,660
    15-01-2022hebriClothingDenim Jeans₹ 4,370
    16-01-2022seethanadiClothingHooded Sweatshirt₹ 5,790
    25-01-2022bengaluruClothingRunning Shoes₹ 9,480
    04-02-2022mumbaiHome AppliancesRobot Vacuum₹ 29,340
    19-02-2022udupiHome AppliancesAir Purifier₹ 36,500
    20-02-2022hebriHome AppliancesCoffee Maker₹ 9,220
    23-02-2022seethanadiBooks"The Hitchhiker's Guide to the Galaxy"₹ 1,109
    23-02-2022bengaluruBooks"Educated"₹ 1,854
    03-03-2022mumbaiBooks"Sapiens: A Brief History of Humankind"₹ 1,419
    08-03-2022udupiFitness EquipmentFitness Tracker₹ 9,220
    09-03-2022hebriFitness EquipmentDumbbell Set₹ 4,370
    10-03-2022seethanadiFitness EquipmentYoga Ball₹ 2,099
    13-03-2022bengaluruKitchen AppliancesInstant Pot₹ 7,300
    14-03-2022mumbaiKitchen AppliancesBlender₹ 32,300
    19-03-2022udupiKitchen AppliancesStand Mixer₹ 21,190
    27-03-2022hebriBeauty and Personal CareMoisturizer₹ 1,495
    05-04-2022seethanadiBeauty and Personal CarePerfume₹ 6,635
    05-04-2022bengaluruBeauty and Personal CareElectric Toothbrush₹ 3,680
    14-04-2022mumbaiToys and GamesLEGO Classic Creative Bricks Set₹ 3,380
    18-04-2022udupiToys and GamesBoard Game₹ 2,569
    28-04-2022hebriToys and GamesAction Figures₹ 974
    30-04-2022seethanadiFurnitureSectional Sofa₹ 88,100
    03-05-2022bengaluruFurnitureBed Frame₹ 14,200
    12-05-2022mumbaiFurnitureDining Table and Chairs₹ 42,980
    12-05-2022udupiOutdoor GearCamping Tent₹ 14,200
    16-05-2022hebriOutdoor GearBackpack₹ 7,220
    25-05-2022seethanadiOutdoor GearSleeping Bag₹ 5,790
    29-05-2022bengaluruAutomotiveElectric Car₹ 25,06,500
    31-05-2022mumbaiAutomotiveBike Rack₹ 11,260
    08-06-2022udupiAutomotiveCar GPS Navigation System₹ 9,660
    14-06-2022hebriPet SuppliesCat Litter₹ 974
    16-06-2022seethanadiPet SuppliesDog Bed₹ 3,680
    19-06-2022bengaluruPet SuppliesPet Grooming Kit₹ 2,274
    23-06-2022mumbaiSports EquipmentRunning Shoes₹ 9,480
    27-06-2022udupiSports EquipmentBasketball₹ 2,274
    27-06-2022hebriSports EquipmentYoga Blocks₹ 974
    18-07-2022seethanadiMusic and InstrumentsAcoustic Guitar₹ 22,390
    20-07-2022bengaluruMusic and InstrumentsDigital Piano₹ 51,350
    26-07-2022mumbaiMusic and InstrumentsWireless Earbuds₹ 11,940
    28-07-2022udupiOffice SuppliesWireless Keyboard and Mouse Set₹ 3,680
    29-07-2022hebriOffice SuppliesDesk Organizer₹ 1,495
    31-07-2022seethanadiOffice SuppliesNoise-Canceling Desk Fan₹ 2,974
    03-08-2022bengaluruTravel AccessoriesTravel Backpack₹ 7,220
    04-08-2022mumbaiTravel AccessoriesPassport Holder₹ 711
    05-08-2022udupiTravel AccessoriesPortable Charger₹ 2,274
    06-08-2022hebriGardening ToolsPruning Shears₹ 1,495
    06-08-2022seethanadiGardening ToolsGarden Hose₹ 2,274
    10-08-2022bengaluruGardening ToolsPotting Soil₹ 974
    10-08-2022mumbaiHealth and WellnessYoga Mat₹ 2,099
    19-08-2022udupiHealth and WellnessMeditation Cushion₹ 3,999
    20-08-2022hebriHealth and WellnessEssential Oil Diffuser₹ 2,974
    21-08-2022seethanadiTech GadgetsSmart Watch₹ 29,340
    25-08-2022bengaluruTech GadgetsVirtual Reality Headset₹ 21,190
    30-08-2022mumbaiTech GadgetsPortable Bluetooth Speaker₹ 5,269
    01-09-2022udupiHome DecorWall Art₹ 3,999
    03-09-2022hebriHome DecorThrow Pillows₹ 1,495
    06-09-2022seethanadiHome DecorFloor Lamp₹ 5,269
    12-09-2022bengaluruElectronicsSmartphones₹ 51,500
    18-09-2022mumbaiElectronicsLaptop₹ 88,100
    18-09-2022udupiElectronicsHeadphones₹ 25,660
    21-09-2022hebriClothingDenim Jeans₹ 4,370
    22-09-2022seethanadiClothingHooded Sweatshirt₹ 5,790
    22-09-2022bengaluruClothingRunning Shoes₹ 9,480
    26-09-2022mumbaiHome AppliancesRobot Vacuum₹ 29,340
    03-10-2022udupiHome AppliancesAir Purifier₹ 36,500
    04-10-2022hebriHome AppliancesCoffee Maker₹ 9,220
    05-10-2022seethanadiBooks"The Hitchhiker's Guide to the Galaxy"₹ 1,109
    10-10-2022bengaluruBooks"Educated"₹ 1,854
    11-10-2022mumbaiBooks"Sapiens: A Brief History of Humankind"₹ 1,419
    19-10-2022udupiFitness EquipmentFitness Tracker₹ 9,220
    21-10-2022hebriFitness EquipmentDumbbell Set₹ 4,370
    30-10-2022seethanadiFitness EquipmentYoga Ball₹ 2,099
    01-11-2022bengaluruKitchen AppliancesInstant Pot₹ 7,300
    04-11-2022mumbaiKitchen AppliancesBlender₹ 32,300
    07-11-2022udupiKitchen AppliancesStand Mixer₹ 21,190
    28-11-2022hebriBeauty and Personal CareMoisturizer₹ 1,495
    30-11-2022seethanadiBeauty and Personal CarePerfume₹ 6,635
    02-12-2022bengaluruBeauty and Personal CareElectric Toothbrush₹ 3,680
    06-12-2022mumbaiToys and GamesLEGO Classic Creative Bricks Set₹ 3,380
    07-12-2022udupiToys and GamesBoard Game₹ 2,569
    07-12-2022hebriToys and GamesAction Figures₹ 974
    07-12-2022seethanadiFurnitureSectional Sofa₹ 88,100
    13-12-2022bengaluruFurnitureBed Frame₹ 14,200
    13-12-2022mumbaiFurnitureDining Table and Chairs₹ 42,980
    24-12-2022udupiOutdoor GearCamping Tent₹ 14,200
    26-12-2022hebriOutdoor GearBackpack₹ 7,220
    29-12-2022seethanadiOutdoor GearSleeping Bag₹ 5,790
    01-01-2023bengaluruAutomotiveElectric Car₹ 25,06,500
    12-01-2023mumbaiAutomotiveBike Rack₹ 11,260
    19-01-2023udupiAutomotiveCar GPS Navigation System₹ 9,660
    23-01-2023hebriPet SuppliesCat Litter₹ 974
    25-01-2023seethanadiPet SuppliesDog Bed₹ 3,680
    25-01-2023bengaluruPet SuppliesPet Grooming Kit₹ 2,274
    29-01-2023mumbaiSports EquipmentRunning Shoes₹ 9,480
    02-02-2023udupiSports EquipmentBasketball₹ 2,274
    04-02-2023hebriSports EquipmentYoga Blocks₹ 974
    05-02-2023seethanadiMusic and InstrumentsAcoustic Guitar₹ 22,390
    10-02-2023bengaluruMusic and InstrumentsDigital Piano₹ 51,350
    15-02-2023mumbaiMusic and InstrumentsWireless Earbuds₹ 11,940
    16-02-2023udupiOffice SuppliesWireless Keyboard and Mouse Set₹ 3,680
    18-02-2023hebriOffice SuppliesDesk Organizer₹ 1,495
    26-02-2023seethanadiOffice SuppliesNoise-Canceling Desk Fan₹ 2,974
    27-02-2023bengaluruTravel AccessoriesTravel Backpack₹ 7,220
    27-02-2023mumbaiTravel AccessoriesPassport Holder₹ 711
    03-03-2023udupiTravel AccessoriesPortable Charger₹ 2,274
    04-03-2023hebriGardening ToolsPruning Shears₹ 1,495
    12-03-2023seethanadiGardening ToolsGarden Hose₹ 2,274
    12-03-2023bengaluruGardening ToolsPotting Soil₹ 974
    13-03-2023mumbaiHealth and WellnessYoga Mat₹ 2,099
  • prashett7's avatar
    prashett7
    Frequent Visitor
    2 years ago

    Please let me know if you have worked on this and are aware that this cannot be achieved in Power BI.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi prashett7 ,

    I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting.

    Thanks for your efforts & time in advance.

     

    Best regards,
    Community Support Team_Binbin Yu

    • prashett7's avatar
      prashett7
      Frequent Visitor
      2 years ago

      I would like to include a slicer for avg revenue per day. Currently it is a measure and I cannot use a measure as input for a slicer. I'm not able to get the accurate average in a calculated column.

       

      Let me know if you still have any questions.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi prashett7 ,

        Sorry, so far, to my knowledge, this may not be achieveable.

         

        Best regards,
        Community Support Team_Binbin Yu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.