Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Trying to automate dates using two different data sources

  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 d...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
deepu299
Advocate V
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      deepu299
      Advocate V
      7 years ago

      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.