time based filter

3 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