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

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.

Community Support

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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors