cancel
Showing results for
Did you mean:
Frequent Visitor

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

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)

1 ACCEPTED SOLUTION
Community Support

Hi, @Magistralis

``````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.

Community Support

Hi, @Magistralis

``````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.

Announcements

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors