Trying to automate dates using two different data sources

[Anonymous]

Sample of the "goal" i'm trying to get but would like the Months to be autopopulated to the current month, previous month and 2 months ago instead of having to continue going in and changing the periods.

Hello!

I am fairly new to the developing side of Power BI and stuck on trying to figure out a way to automate the Fiscal Periods on a new Sales Forecasting report.

The actual sales data is pulling from a sql table and the forecast is coming from an excel spreadsheet on sharepoint.  There's a few more columns created to show the variance % and the difference between 3 time periods, (Current Fiscal Period, Previous Fiscal Period and 2 Months Ago) with hopes to provide a 3 month rolling of sales/forecast data.

Here's a screenshot of the date formats from the SQL below which is currently "filtered", however I would like it to always provide the current fiscal period and 2 months prior. Is there a way to do that using M language or a dax query?

sample data from forecast template

Any and all suggestions are greatly appreciated! 🙂

[deepu299]

@Anonymous  You can create another column something like "CurrentMonths" whose value will be 1 for these 3 months and BLANK() for rest of the months. This will be updated whenever you refresh the data. Now you can use this column as a filter on the page or the visual depending on your needs. The filter will be "CurrentMonths = 1" and hide it. Since the value 1 will be always on the 3 months you need, you will get only that data. 

Does this colve your use case?

[Anonymous]

Thanks for providing this information! So I created a custom column but had to use a formula to provide the correct current month for the fiscal year period as the year begins in September for my industry. Here is the column I've created to provide the current fiscal period:

Current (FY Period) = If(MONTH(TODAY())<9,MONTH(TODAY())+4,MONTH(TODAY())-8). I then also created 2 more new columns to show the previous month and 2 months ago, but I'm still stuck on having it automated as the relationship for the fiscal periods is pulling from a direct query to get the sales data, but I want it to look at the excel spreadsheet to only filter on the periods provided from the forecast.

[deepu299]

@Anonymous  I meant to create one column in the dataset. something like this, so you can use that column as a filter. I did based on months but you can modify the variables if you want dates or YYYYMM format. 

=
VAR MinMonth= FORMAT(EOMONTH(TODAY(), 0), "MM")
VAR MaxMonth = FORMAT(EOMONTH(TODAY(), +3), "MM")
RETURN

IF(<Month in Table> >= MinMonth && <Month in Table> <= MaxMonth, 1, blank())

[Anonymous]

I did that a couple of times and kept getting errors but finally got it to error out with the code below 🙂

Var Month =

VAR MinMonth = 'Current Fiscal Dates'[Current (FY Period)]

VAR MaxMonth = [FY Period (2 Months Ago)]

RETURN

IF('Current Fiscal Dates'[Current (FY Period)] >= MinMonth && 'Current Fiscal Dates'[FY Period (2 Months Ago)] <= MaxMonth, 1, blank())

So now, I just use this as a filter and it will only return those dates?

[deepu299]

Right, my expectation is the value of "Var Month" will be 1 only for the records in those 3 months in the whole dataset and it should be Blank for other rows. Do test it from your side and add the filter as needed. You can hide it from the filter pane so it's not visible to users.