time based filter

4 Topics

Please Urgent Help with Creating Period blocks from Start Date Column
Hello, Please, I desperately need help with any tips or recommendations on how to target this problem. I have one requirement in a cencus report I built to add actuals to target services of patients. What I mean by this is, if I have a patient who is required to have their services done 2 times monthly, I would hope from the start date to 30 days from that start date, they have been attended to, 2 times giving me 100% of my actual to target. So again, if my expected services to be done is set to be 2 services monthly, my actual services in that time period from the start date e.g. 1/1/2022 - 1/31/2022 is 1, then that I would have been attended to just 50% of the time. Is there a way anyone could advise me how to target this problem given the below data, and if so, would you recommend some ideas please? I really need help with how I can build some of this period blocks so if its - monthly, +30 days from start date - weekly, +14 days from start date -bi-monthly, +60 days from start date - quarterly, +90 days from start date and so on I had to model both of these fact tables in power bi. I can also perform a join in the database to merge all to one, of which I plan to do. Its just a bit challenging considering the frequency table has a start & end date, and the service table has just a calendar date. I guess I can join both tables on their patient_id and service_date where it falls between the from and to date columns in the frequency table. But please advise, anything would be greatly appreciated. I really need inputs on how to get this one visual out. THANK YOU so much in advance. Sample raw data frequency_table: patient_id patient_name start_date end_date expected_frequency frequency_code frequency_type 1001 Sara S 6/18/2020 12/31/2020 2 3 Monthly 1001 Sara S 1/1/2021 3/1/2021 1 4 Bi-Monthly service_table: is_attended code 1 for Yes, 0 for No. patient_id patient_name date is_attended 1001 Sara S 6/18/2020 1 1001 Sara S 6/30/2020 1 1001 Sara S 7/18/2020 1 1001 Sara S 7/31/2020 0 1001 Sara S 8/18/2020 1 1001 Sara S 8/31/2020 0 1001 Sara S 9/18/2020 1 1001 Sara S 9/30/2021 1 1001 Sara S 10/18/2021 1 1001 Sara S 10/29/2021 1 1001 Sara S 11/18/2021 0 1001 Sara S 12/5/2021 0 1001 Sara S 12/18/2021 0 1001 Sara S 12/27/2021 0 1001 Sara S 1/1/2021 1 1001 Sara S 2/1/2021 0 1001 Sara S 3/15/2021 0 Expected Results: to_frequency_date is a sample column for the period date from the frequency "start_date". patient_id patient_name from_date to_frequency_date expected_frequency Actual frequency_type % Actual to Target(freq) 1001 Sara S 6/18/2020 7/17/2020 2 2 Monthly 100% 1001 Sara S 7/18/2020 8/17/2020 2 1 Monthly 50% 1001 Sara S 8/18/2020 9/17/2020 2 1 Monthly 50% 1001 Sara S 9/18/2020 10/17/2020 2 2 Monthly 100% 1001 Sara S 10/18/2020 11/17/2020 2 2 Monthly 100% 1001 Sara S 11/18/2020 12/17/2020 2 0 Monthly 0% 1001 Sara S 12/18/2020 12/31/2020 1 0 Monthly 0% 1002 Sara S 1/1/2021 3/1/2021 1 1 Bi-Monthly 100% Please let me know if you have any questions, happy to clarify.
Solved
yve214
3 years ago Place DAX Commands and Tips
524Views
0likes
1Comment
Time Evolution of Inventory Using DAX
Hello Everyone, I am attempting to create a running evolution of inventory based on demand. The problem is essentially this: I want to create a running log of inventory based on consumption of product as time goes on. This is to answer the questiion, in a worst case scenario, and we do not receive the material we need, when will we run out of product. I believe this is easier to accomplish in DAX, but if anyone has a solution in PowerQuery as well, I would be open to hear it. If that was not clear, please refer to the chart I copied and pasted below. Please make note of the two different part numbers. Date Part Number Demand Current Inventory (current month) Running Inventory 7/1/2022 123 500 4700 4200 7/1/2022 345 500 1000 500 8/1/2022 123 600 3600 9/1/2022 123 550 3050 9/1/2022 345 500 0 10/1/2022 123 440 2610 11/1/2022 123 700 1910 12/1/2022 123 550 1360 1/1/2023 123 575 785 2/1/2023 123 600 185 The inventory only appears in the month of July because in PowerQuery I merged the inventory to appear only in the same month as the current demand month, which in this case is July 2022. Any advice would be a great help, thank you!
Solved
jt1999
4 years ago Place DAX Commands and Tips
693Views
0likes
2Comments
Mesure to filter time periods
Hello guys, I think i had a solution here 1 year ago but i lost my pbix. Now trying to create it again. I want to create a slicer in addition to slicers year-month. In this slicer I want to have: Month YTD previous 6 months previous 12 months Previous month YTD Sameperiodlastyear 12 months for previous period All proposed solutions on youtube give me the dax with Sum('Sales'[Ammount]) but I want to apply the periods to all different mesures without creating DAX for each one. Now I'm creating my different sum mesures, than I will put in the new FieldParameter feature, and use this FieldParameter as "Sum('Sales'[Ammount])" in my timeperiods DAX, I could create a calculation group in Tabular editor but trial is finished. However I'm sure there is a much more elegant and simple solution, all I need is just to filter the DateTable. Thank you for any advice
Anonymous
4 years ago Place DAX Commands and Tips
539Views
0likes
1Comment
Displaying Data for every N Months (Every 3 Months , Every 12 Months)
The Use Case : The Slicer has 4 options to select data from : 1. Daily - Select Start and End Date in the slicer - Display the data based on the date Selection 2. Monthly - Select Start and End Date in the slicer - Display the data for All Month End Dates between the Range 3. 3 Months - Select Start and End Date in the slicer - Display the data for All Month End Dates between the Range BUT now the difference between the month end dates should be 3 Months and not 1 month. 4. 12 Months - Select Start and End Date in the slicer - Display the data for All Month End Dates between the Range BUT now the difference between the month end dates Should be 12 Months. For futher reference please find the attached screenshots of different output scenarios
Udit_C
4 years ago Place Custom Visuals Development Discussion
1.1KViews
0likes
0Comments