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
Community Support Team
Community Support Team

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.
7 REPLIES 7
Community Support Team
Community Support Team

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

 

 

Community Support Team
Community Support Team

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

Community Support Team
Community Support Team

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

 

 

Community Support Team
Community Support Team

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.