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

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'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?