topic Re: Translating pay ending date into individual or daily date in DAX Commands and Tips

Translating pay ending date into individual or daily date

Rabi — Mon, 12 May 2025 23:47:22 GMT

Hi Everyone

I have a table like this which i want to translate as table below. I do have a table that has individual date and pay ending date

Initial Table
Location	pay ending date	value
Location	15/01/2025	200

Achieve below

Achieve this in a new table
Location	Date	value
Location	2/01/2025	14.28571
Location	3/01/2025	14.28571
Location	4/01/2025	14.28571
Location	5/01/2025	14.28571
Location	6/01/2025	14.28571
Location	7/01/2025	14.28571
Location	8/01/2025	14.28571
Location	9/01/2025	14.28571
Location	10/01/2025	14.28571
Location	11/01/2025	14.28571
Location	12/01/2025	14.28571
Location	13/01/2025	14.28571
Location	14/01/2025	14.28571
Location	15/01/2025	14.28571
		200 (True)

Thanks !!

Re: Translating pay ending date into individual or daily date

Jihwan_Kim — Tue, 13 May 2025 02:58:31 GMT

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below, and hard-coded the start date = January 02nd

Please check the below picture and the attached pbix file.

expected result table = VAR _calendar = ADDCOLUMNS( CALENDAR( DATE(YEAR(MIN(data[pay ending date])), 1, 1), DATE(YEAR(MAX(data[pay ending date])), 12, 31) ), "Year", YEAR([Date]), "Month number", MONTH([Date]), "Day of month", DAY([Date]) ) VAR _t = GENERATE( data, FILTER( _calendar, [Year] = YEAR(data[pay ending date]) && [Month number] >= 1 && [Month number] <= MONTH(data[pay ending date]) && [Day of month] >= 2 && [Day of month] <= DAY(data[pay ending date]) ) ) VAR _average = ADDCOLUMNS( ADDCOLUMNS( _t, "@rowcount", COUNTROWS(FILTER( _t, data[Location] = EARLIER(data[Location]) )) ), "@value", FORMAT( DIVIDE( data[value], [@rowcount] ), "#,#0.00000" ) ) RETURN SUMMARIZE( _average, data[Location], [Date], [@value] )

Re: Translating pay ending date into individual or daily date

v-sgandrathi — Wed, 14 May 2025 07:36:19 GMT

Hi Rabi,

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!