Forum Discussion

Frequent Visitor

2 years ago

Link date_start and date_end to calender table

In a matrix I want to count all id's per month and year that are between date_start and date_end. So when date_start is 2023/08/23 and date_end is 2023/11/15 than august should count 1, sept should ...

Icon for Community Champion rank

Community Champion

2 years ago

noobtopowerbi
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Hard to be specific without knowing your data. If we are just talking about a Dates table or something, then here's a guess:

Measure = 
  VAR __MinDate = MIN('Table'[Date])
  VAR __MaxDate = MAX('Table'[Date])
  VAR __Table = 
    ADDCOLUMNS(
      FILTER( 'Table', [Date] >= __MinDate && [Date] <= __MaxDate ),
      "__Month", MONTH( [Date])
    )
  VAR __Table1 = SUMMARIZE( __Table, [__Month] )
  VAR __Result = COUNTROWS( __Table1 )
RETURN
  __Result