cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
palo173 Regular Visitor
Regular Visitor

List of values from fact table with date range

Hi and thank you for each help. 

 

For Example:

I have Fact_table with validity period.

Id_Contract      Date_From       Date_To    .....

1                       01.01.2018      08.01.2018

2                       05.01.2018      15.01.2018

3                       10.01.2018      12.01.2018

4                       13.01.2018      22.01.2018

5                       20.01.2018      30.01.2018

 

Date_Dimension

Date_Id    Year    Month  .....

1.1.2018   2018    1

2.1.2018   2018    1

3.1.2018   2018    1

4.1.2018   2018    1

....

 

 

I want to create in PowerBI visualisation with list of contracts, but with the option of setting specific dates or periods. HOW TO DO IT??

 

So when I set up filter for example: Date_Id from 10.1.2018 to 13.1.2018 

 

Result in visualisation will be 

List_Of_Contracts 

2

3

4

 

because in selected period from 10.1.2018 to 13.1.2018 are valid only contracts with id 2,3 and 4.

 

I spend lot of time for searching, but I know only count of Contracts. In case above it is count of contracts in selected range =3.

 

Actual_Count =
CALCULATE( DISTINCTCOUNT ( Fact_Table [Id_Contract] );
           FILTER ( GENERATE ( SUMMARIZE ( Fact_Table;
                                           Fact_Table[Date_From];
                                           Fact_Table[Date_To]
                                         );
                               DATESBETWEEN ( Date_dimension[Date_Id];
                                              Fact_Table[Date_From];
                                              Fact_Table[Date_To]
                                            )
                             );
                   CONTAINS ( VALUES ( Date_dimension[Date_Id]);
                                        Date_dimension[Date_Id];
                                        Date_dimension[Date_Id]
                            )
                ))

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: List of values from fact table with date range

Hi @palo173,

 

I would suggest you create a measure and filter the values of the measure. You can add it in the visual or add it to the Visual Level Filter.

Measure =
IF (
    MIN ( Fact_Table[Date_To] ) >= MIN ( 'Date_Dimension'[Date] )
        && MIN ( 'Fact_Table'[Date_From] ) <= MAX ( 'Date_Dimension'[Date] ),
    1,
    BLANK ()
)

List_of_values_from_fact_table_with_date_range

Best Regards,
Dale

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

View solution in original post

7 REPLIES 7
v-jiascu-msft Super Contributor
Super Contributor

Re: List of values from fact table with date range

Hi @palo173,

 

I would suggest you create a measure and filter the values of the measure. You can add it in the visual or add it to the Visual Level Filter.

Measure =
IF (
    MIN ( Fact_Table[Date_To] ) >= MIN ( 'Date_Dimension'[Date] )
        && MIN ( 'Fact_Table'[Date_From] ) <= MAX ( 'Date_Dimension'[Date] ),
    1,
    BLANK ()
)

List_of_values_from_fact_table_with_date_range

Best Regards,
Dale

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

View solution in original post

palo173 Regular Visitor
Regular Visitor

Re: List of values from fact table with date range

Thank you.

I try it but it doesn't work in real situation. I attached a screen.
Before and after add Measure.

It works only my previous script for counting contracts in graph.

So in the picture are period from 1.10.2007 to 31.12.2007. So result should be 5 contracts 10563,10567,10569,12548 and 12549.

 

I make some mistake? What is wrong for list of contracts?

 

Thank you.

 

first.JPGsecond.JPG

 

 

v-jiascu-msft Super Contributor
Super Contributor

Re: List of values from fact table with date range

Hi @palo173,

 

Which field does the slicer have? Seems it isn't [Date_ID]. It's better to use the same field in the measure "Actual_Att".

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
palo173 Regular Visitor
Regular Visitor

Re: List of values from fact table with date range

Slicer have the same field DIM_DATE[DATE_ID] as in measure "Actual_Att".

v-jiascu-msft Super Contributor
Super Contributor

Re: List of values from fact table with date range

Hi @palo173,

 

Are there any relationships? Can you share the file?

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
palo173 Regular Visitor
Regular Visitor

Re: List of values from fact table with date range

You are right. 

There si  problem with my "Data Model", specifically with relationship between DIM_DATE and FCT_CONTRACT_VALUE. There have to be obviously no relationship. When I remove this relationship it works fine, but another graph with data from  FCT_CONTRACT_VALUE is broken. 

 

My question is: I have to have one separated "Date Dimension" for FCT_CONTRACT_VALUE ane another one "Date Dimension" for FCT_CONTRACT_VALUE? So 2 identical Date Dimension tables? 

Or I have to have only inactive ralationship to one "Date Dimension"?

I hope that my question is clear.

 

Thank you

 

three.JPG

 

 

Highlighted
v-jiascu-msft Super Contributor
Super Contributor

Re: List of values from fact table with date range

Hi @palo173,

 

I'm afraid so. I would suggest you create a new date table. Why? Because there are many days in the interval rather than concrete existences. For example,

from                to

2018-01-01   2018-01-31

the date 2018-01-15 is qualified. But if you create a relationship or two relationships with one is inactive, the date 2018-01-15 is ignored. Because we can't remove the impact of relationship in the visuals. 

List_of_values_from_fact_table_with_date_range

 

 

Best Regards,
Dale

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,120 guests
Please welcome our newest community members: