Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with data between two dates

Hello, I am fairly new to BI and wondering if anyone might be able to offer me some advice on a report I am trying to finish. I am connected to our Dynamics 365 service, pulling in data for project...
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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