Use only Overlapping Months

[Rehan]

I have a requirements where I  have multiple extract dates in a single dataset and each extract date has multiple months .

For example :

Extract Date = May 2019 has Data for May 2019, June 2019, July 2019 up to Dec 2019

Extract Date = Mar 2019 has Data for Mar 2019, Apr 2019 ,May 2019, June 2019 up to Oct 2019

The requirement is I need data for only overlapping months between the 2 extract dates . In this case the overlapping months will be May 2019 Until Oct 2019.

How can I accomplish this ? Idealy I would like to create some sort of a flag in the script to get the overlapping months between 2 extract Dates.

[sunny_talwar]

Here you go

[Rehan]

GR8 Thnx I guess the first step is successful, now the next step is  If I don't have any selections for the ETL Date, I would like to see the common months data for the most recent ETL Date and one Prior Date. So in this case By default I want to see May and Jan 2019 data for the common months between them?

[sunny_talwar]

Try this

Sum({<[Forecast Date] = {"=Count(DISTINCT {<[ETL Date] = {[$(=Date(Max([ETL Date])))], [$(=Date(Max([ETL Date], 2)))]}>} [ETL Date]) = 2"}, [ETL Date] = {"$(=Date(Max([ETL Date])))", "$(=Date(Max([ETL Date], 2)))"}>} Amount)

[Rehan]

This Expression Rocks, giving me exactly what I want for one of the requirements. One last requirement is  that  by default I also want to show  the Amount for only the Current ETL Date with Matching months from the Previous ETL Date  and also show the Previous ETL Date amount only with Matching months from the current ETL Date. 

[sunny_talwar]

Isn't that what it is doing?

Here I am guessing that your default is no selection in ETL Date. If that is not what your default is, then what is your default?

[Rehan]

Yes this is one of the requirements but this result set is giving me the sum of ETL Date 1, and ETL Date 2 for the common  months. My another requirement is that I want the sum of ETL Date 1 only for  the common Months between ETL Date 1 and ETL Date 2 and 

the sum of ETL Date 2 only for  the common Months between ETL Date 1 and ETL Date 2.

Both these are seperate KPI's. I think you are very close this solution also

[Rehan]

Also when I make a selection in the ETL DAte the expression NUlls out

[Rehan]

The qvw you sent was perfect but one last  requirement is  I would like to calculate the difference between the 2 Selected ETL dates. How can I do that with this expression? I mean 

Sum(Max ETL Date) - Sum(Previous ETL Date)