Time slot in x-axis

[srivallyK_14]

Hi All,

I have two datasets Timeslot which contains timerange and slot fields, MainSet contains DateTime and client_Id.
Here I want to show slot number in x-axis, so that I can check the sales for each slot which defined in my Timeslot dataset. I have tried to create lookup for these which did not helped me. 
https://www.dropbox.com/sh/vquoveulzrb8trd/AAAO5el6H0FkNitbnnP9U1R9a?dl=0
Please help me on this.

Regards,
Srivalli

[v-henryk-mstf]

Hi @srivallyK_14 ,

I am a little confused about your needs. Do you want to get the total sales value corresponding to the time from "From time" to "to time", right? So what is the role of the "Client Id" field in the Mainset table?

Can you provide more information, preferably to provide the expected expected results, so that I can do further testing for you.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[srivallyK_14]

@v-henryk-mstf I have placed my required in detail here.
https://www.dropbox.com/scl/fi/td52uxm0scupr5lmn4vzm/TimeSlotDoc.docx?dl=0&rlkey=ikfrvh4dplbtk7fbtxy...

Regards,
Srivalli

[v-henryk-mstf]

Hi,

You can try the following formula:

Filter = 
CALCULATE (
    SUM ( MainSet[Sales] ),
    FILTER (
        MainSet,
        MainSet[Time] >= MAX ( Timelot[FromTime] )
            && MainSet[Time] <= MAX ( Timelot[ToTime] )
    )
)

Then put the field in the corresponding position of the visual:

Here is the sample pbix file.

Since the data you provided is incomplete and the sales corresponding to the date that meets the conditions are empty, I cannot fully reproduce your expected results. But you can follow my steps above to test.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[srivallyK_14]

@v-henryk-mstf I again placed the files and expected output in the link
https://www.dropbox.com/scl/fi/m4387c8hs8np7kf5bvnqu/MainSet.xlsx?dl=0&rlkey=ebftgek9jxrmm8fjxrdlue3...
https://www.dropbox.com/s/z8gunzrbkopncho/Timeslot.PNG?dl=0
I got the above output because i have created the timekey (extracting hour and minute from time)for both the datasets and give the relationship(which was not getting proper output as expected as we have 2 date fields in the dataset).And the Time slot range might vary like 5, 10, 15 which was there in TimeInterval slicer. If we select 15 from TimeInterval then we will get 96 slots in the slot slicer, sameway if we select 10 from slicer, we will get 144 slots.
https://www.dropbox.com/s/2gguagugm9g7oo1/Slot%20Range.PNG?dl=0
Slots should be defined based on the date from above link.

Thanks,
Srivalli

[amitchandak]

@srivallyK_14 , Create a time table with slots you want as suggested in this blog. Separate out time from datetime and join with that.

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

[srivallyK_14]

@amitchandak I want slot field in x-axis from the dataset TimeSlot. So that I can check Sales data for the selected slot from the filter(slot field as a slicer)