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

Filter a table from two date columns from a another Table

Hi Guys

 

I have a Sales Tables  and a Project Schedule Table with Start and End Dates. Basically I want to calculate the sum of Qty by Site from my sales table during the Project Phase in months . 

 

To Illustrate

 

Site A has a  Project Start Date of 05/01/2018 and an End Date of 04/02/2018.  

Essentially the project impact is for Jan & Feb (2 months) disregarding difference in Days between two dates and then I want to sum the qty for these two months for Site A.  As shown below Site A has a qty of 91 (For Jan) and 79 (Feb) giving a total of 170.

Sample Data.pnghttps://drive.google.com/file/d/1pnTmBzKNghJ9WKKRk8y3T8Lmwv9EF7Is/view?usp=sharing

 

 

 

In order to achieve the aforesaid, I understand that ,

 

1) I need to Filter the the Sales Table by adopting either the Filter or CalculateTable

2) Nesting the above table refernce within SUMX funciton. 

 

I have attempted to write the measure but been stuggling to get it thorugh.  Any help is  much appreciated.

 

Sample PBIX is attached.

 

Thanks

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter a table from two date columns from a another Table

Hi,

 

You are welcome.  If my reply helped, please mark it as Answer.

5 REPLIES 5
Community Support Team
Community Support Team

Re: Filter a table from two date columns from a another Table

Hi @Krishna_Mysore,

 

If you mean filter sales table based on related 'project' date range and calculate the summary value which has same site and account, you can try to use to following formulas.

 

Calculate table:

FilteredTable = 
FILTER (
    'Sales Table',
    [Period]
        IN CALENDAR (
            MINX (
                FILTER ( 'Project Schedule', [Site] = EARLIER ( 'Sales Table'[Site] ) ),
                'Project Schedule'[Start Date]
            ),
            MINX (
                FILTER ( 'Project Schedule', [Site] = EARLIER ( 'Sales Table'[Site] ) ),
                'Project Schedule'[End Date]
            )
        )
)

Measure:

Measure =
CALCULATE (
    SUM ( FilteredTable[Value] ),
    ALLSELECTED ( FilteredTable ),
    VALUES ( FilteredTable[Site] ),
    VALUES ( FilteredTable[Account] )
)

22.PNG

 

Regard,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: Filter a table from two date columns from a another Table

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

Krishna_Mysore Regular Visitor
Regular Visitor

Re: Filter a table from two date columns from a another Table

Thanks Ashish

Super User
Super User

Re: Filter a table from two date columns from a another Table

Hi,

 

You are welcome.  If my reply helped, please mark it as Answer.

Krishna_Mysore Regular Visitor
Regular Visitor

Re: Filter a table from two date columns from a another Table

Thanks @v-shex-msft.  I had to slightly modiy the measure before adopting it. 

Much appreciated