FIlter Second Query By Results of First

[bgarland]

I'm not sure why I'm having trouble figuring this out....you'd think it would be easy, or at least more intuitive. My fact table has millions of sales records, each one containing the Fiscal_Month of the sale (e.g. 6/1/2018, 7/1/2018). I only want to get the last six months of sales, so to avoid hard-coding dates my first query is against a calendar table that contains Fiscal_Month_Relative (e.g. 0=this month, -1=last month, -2=two months ago). So my first query filters where Fiscal_Month_Relative between -5 and 0, which returns Fiscal_Month values of 7/1/2018 through 2/1/2018. Now I want to use those values to get records from my Sales fact table.

[Anonymous]

Hi @bgarland

I assume you have set the relationship between these 2 tables. In that case , you can create visuals from fact table , and pull Fiscal_Month_Relative to visal filter and apply it should be >= -5 and <= 0.

if you need all visuals should be displayed for this date filter,uset eh above filed in page filter.

Alternatively, you can creaet this measure to calculate last 6 month sales.

CALCULATE(SUM(Sales_Fact.Sales),DATEADD(Dates[Date],-6,MONTH)

Thanks
Raj

[bgarland]

Yes, I could do that but that would be after I loaded millions of rows of data that I don't need. I trying to limit the rows from the fact table that are being loaded into PowerBI.

This is how I would do it in SQL:

SELECT AL1.*

FROM dbo.Sales_Detail AL1, dbo.Monthly_Calendar AL2

WHERE (AL1.Fiscal_Period_Date=AL2.Fiscal_Period_Date) AND (AL2.Fiscal_Period_Relative BETWEEN -5 AND 0)

[Anonymous · Accepted solution]

Oh ok, you are asking in Power Query.

Merge your date table with Fact Table.

Click on Fact Table in power Query/ Query Editor-> Home-> Merge Queries and merge your fact table and Date table.

You will see all columns in Fact table and one new column with value "Table" in all rows, next to the new column header, you can see a expand symbol, click that and select the Relative finance month field. In that column apply filter >=-5 and <=0.

your query will bring only last 6 months of data only.

Hope this helps.

Thanks

Rja

[bgarland]

Thanks for your help but I couldn't figure it out. My fault for being a novice, but it should still be easier. I just ended up using the SQL statement, which was easier.

[v-chuncz-msft]

@bgarland,

By the way, you may help accept the solution above. Your contribution is highly appreciated. Besides, you may try DirectQuery if possible.