cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors