Data visualization for period of time (e.g. only for 3 months),

[Magistralis]

Good day beautiful people,

I have a problem how to create a visualization for below tables.

Main:

 

Helper:

Formulas:

Start Date = 64 + Days Frozen + Posting Start Date

Latest Active Date = IF(End Date = blank, Today(), End Date)

Result:

What I would like to show, is a colum chart where month by month my data will be presented. 

If my ID's Start Date is 3/18/2018 and Latest Adctive Date is 6/23/2022, I would like to show this ID in March, April, May and June.

For second example, SD 4/6/2022 and LAD 5/23/2022 I would like to show this in April and May only.

Start Date won't be higher than a Latest Active Date.

I have tried below formula:

_Visiu1b =

CALCULATE(

                   COUNT(Table1[ID]),

                   FILTER(Table1,

                                    Table1[Start Date] <= max('Calendar'[Date]) &&

                                    Table1[Latest Active Date] >= min('Calendar'[Date])

                              )

                     )

Calendar[Date] is a column with dates day by day, starting 1/1/2018 - 12/31/2023

For now I was able to present it only for SD's month or LAD's month which is absulutely not correct since in some cases I need multiple months.

(example screens were added from excel, since it was faster for me to create a sample)

[v-zhangti · Accepted solution]

Hi, @Magistralis 

Please try the following methods.

Table 1 = 
SUMMARIZE (
    'Table',
    'Table'[ID],
    "Start Date",
        64 + SELECTEDVALUE ( 'Table'[Days Frozen] )
            + SELECTEDVALUE ( 'Table'[Posting Start Date] ),
    "Latest Active Date",
        IF (
            SELECTEDVALUE ( 'Table'[End Date] ) = BLANK (),
            TODAY (),
            SELECTEDVALUE ( 'Table'[End Date] )
        )
)

_Visiu1b = 
CALCULATE(COUNT('Table 1'[ID]),
                   FILTER('Table 1',
                                    [Start Date] <= max('Calendar'[Date]) &&
                                    [Latest Active Date] >= min('Calendar'[Date])
                              )
                     )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.