Forum Discussion
Help with data between two dates
hi Anonymous
You could try this simple way
step1:
Use CALENDARAUTO Function to create a dim date table as below:
Date = ADDCOLUMNS(CALENDARAUTO(),"YearMonthNo",YEAR([Date])*100+MONTH([Date]),"Year Month", YEAR ( [Date] )& " " & FORMAT ( [Date], "mmmm" ) )Step2:
Use this logic to create a new table
New table = FILTER ( GENERATE ( 'Table', SUMMARIZE ( 'Date', 'Date'[Year Month], 'Date'[YearMonthNo] ) ), [YearMonthNo] >= YEAR ( [Start Date] ) * 100+ MONTH ( [Start Date] ) && [YearMonthNo] <= YEAR ( [end date] ) * 100 + MONTH ( [end date] ) )Result:
and you could also have a look this post that similar to your case.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
here is my sample pbix file, please try it.
Regards,
Lin
OK thank you! I understand the concept. Is there a formula I could use to achieve this without having the manually enter them? I habe many more than two so it would be nice to have it automated as more are entered.
hi Anonymous
You could try this simple way
step1:
Use CALENDARAUTO Function to create a dim date table as below:
Date = ADDCOLUMNS(CALENDARAUTO(),"YearMonthNo",YEAR([Date])*100+MONTH([Date]),"Year Month", YEAR ( [Date] )& " " & FORMAT ( [Date], "mmmm" ) )Step2:
Use this logic to create a new table
New table =
FILTER (
GENERATE (
'Table',
SUMMARIZE ( 'Date', 'Date'[Year Month], 'Date'[YearMonthNo] )
),
[YearMonthNo]
>= YEAR ( [Start Date] ) * 100+ MONTH ( [Start Date] )
&& [YearMonthNo]
<= YEAR ( [end date] ) * 100 + MONTH ( [end date] )
)Result:
and you could also have a look this post that similar to your case.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
here is my sample pbix file, please try it.
Regards,
Lin