dynamic date table based on min and max of date column
I would like to create a date table that is based on the customer's "Sign-up Date." As I do not wish to have dates in my pivot table from before the first sign-up or after the last sign-up, I want the date table to be dynamic and grow as more dates are added. I could certainly "hard code" the start date of the table since it would coincide with the company's opening, but I need the end date to go to the last sign-up date and not show anything in the future. How can I create a Date Table that grows based on the MAX date found in the "Sign-up" column? Note: I typically just use a linked table from Excel for my date tables in PowerPivot but this has proven problematic as I need to include ALL dates between min and max sign-up in my columns and rows even if there is no data found for them when I slice the data (i.e the resulting pivot table needs to have the same number of columns and rows regardless of the slicers applied).DAX finding dates
Hello, I need to calculate 2 values using DAX function. 1. Calculate the penultimate date for product id 2. Calculate the quantity for a given date and product id. an example table with data is attached. I will be grateful for your help. id prod date qty 82 17.04.2023 4 801 17.04.2023 5 632 17.04.2023 11 632 17.04.2023 11 5583-2 17.04.2023 3 5583-1 17.04.2023 3 5536 17.04.2023 30 5535 17.04.2023 9 5533 17.04.2023 9 5531 17.04.2023 9 5528-1 17.04.2023 20 5527-4 17.04.2023 1 5526-4 16.04.2023 6 5526-3 16.04.2023 6 5526-1 16.04.2023 6 5525-3 16.04.2023 20 5525-1 16.04.2023 20 5523-2 16.04.2023 26 5522 16.04.2023 4 5521 16.04.2023 4 5517 16.04.2023 4 5516 16.04.2023 8 5515 16.04.2023 5 5510-5 16.04.2023 4 5486 16.04.2023 12 5485 16.04.2023 12 5481 15.04.2023 12 5423 15.04.2023 2 5421 15.04.2023 1 5415 15.04.2023 10 5309-1 15.04.2023 1 5280 15.04.2023 10 5261 15.04.2023 2 525-2 15.04.2023 3 525-1 15.04.2023 5 524 15.04.2023 96 5226-1 15.04.2023 3 5220-2 15.04.2023 6 522 15.04.2023 96 5219-1 15.04.2023 10 5217-1 15.04.2023 5 5215-2 14.04.2023 1 518-2 14.04.2023 30 518-1 14.04.2023 50 5043-5 14.04.2023 1 5043-4 14.04.2023 1 5043-3 14.04.2023 4 5043-1 14.04.2023 1 5041-3 14.04.2023 1 5041-1 14.04.2023 6 5040-3 14.04.2023 2 5038-4 14.04.2023 1 4991-1 14.04.2023 1 492 14.04.2023 2 485 14.04.2023 8 4814 14.04.2023 84 478 14.04.2023 30 472 14.04.2023 2 471 14.04.2023 10 468 13.04.2023 10 467 13.04.2023 30 465 13.04.2023 10 4647 13.04.2023 24 4645 13.04.2023 48 4614 13.04.2023 10 4575 13.04.2023 2 4450 13.04.2023 5 4245 13.04.2023 12 4224 13.04.2023 10 4207-2 13.04.2023 1 4132 13.04.2023 10 4119 13.04.2023 5 3895 13.04.2023 8 3868 13.04.2023 4 3863 13.04.2023 6
Fiscal week not on the first day of month
Hi all, I'm trying to add Fiscal Week and Month to my date table. It starts on October. I used blog post by ChandeepChhabra which I found on a solved question on this website, to generate the Fiscal Week column and it worked but not my desired result. Question Link - https://community.powerbi.com/t5/Desktop/Creating-a-fiscal-week-column/m-p/556549 Blog Post Link - https://www.goodly.co.in/calculate-fiscal-week-in-power-bi/ This was the result I get when I used it. My desired fiscal week is a little different, it is not on the first day of the month. Here's an example: As you can see, the first week starts on the 3rd of Oct where as the result I got from refering to the post starts on 1st of Oct. If the first 2 days of the month is at the end of the week then it is considered to be the week of previous month. I would also like to add another column for the Month based on the fiscal week. Below is my desired Dim Date table. Date Work Week Month (WW) 10/1/2021 12:00:00 AM WW 52 September 10/2/2021 12:00:00 AM WW 52 September 10/3/2021 12:00:00 AM WW 1 October 10/4/2021 12:00:00 AM WW 1 October
