Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Last period Data based on Date/Quarter/Month/Week Filter selection

Hi,

 

 I am new to Power BI. I got one requirement to show data for last 4 quarters data in a table. So if user selects Date/Quarter/Month/Week, I have to show resepctive data. i.e if user selects Date: I have to show last 365 days data. If user selects Quarter: Last 4 quarters data. if he selects Month: last 12 months data and in case of week: Last 52 weeks of data.

I got the result for dates:

OIF_Value_EUR_Calc =
CALCULATE (
SUM ( V_OPPORTUNITIES_OIF[OIF_Value_EUR] ),
DATESINPERIOD ( V_OPPORTUNITIES_PERIOD[Created_Date], MAX (V_OPPORTUNITIES_PERIOD[Created_Date]),-365, DAY )
)

 

But if user selects any other filter, how should I show the respective data.

I would appreciate any help on this.

Thanks!

 

Regards,

Poonam

Need Help
  • Seward12533's avatar
    Seward12533
    8 years ago

    Its all about filter context, Try creating a lookupdate table with Just YEARMONTHSHORT values

    YEARMONTHS = VALUES(date[YearMonthShort])

    Relate that to your data table and set your slice on that.

     

     

    If doesn't work, you could try NOT using a SLICER to select the month but instead use a disconnected slicer to have user select month, date or whatever and then use that SELECTEDVALUE of what the user selectes as teh desired period in your measures. 

13 Replies

  • Seward12533's avatar
    Seward12533
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Use a disconnected slicer to harvest the users choice. 

    • Create a table using Enter Data
    • Do NOT link or relate this table to your data
    • Write a measure to harvest the users choice
    • Write a Dynamic Measure using Switch(TRUE()) to calcualte based on that choice
    Period
    Day
    Week
    Month
    Quarter
    Year

     

    Selected Period = SELECTEDVALUE(Periods[Period],"Day") // defaults to day if nothing selected
OIF_Value_EUR_Calc  = Switch(TRUE(),
    [Selected Period]="Day",  calc for day,
    [Selected Period]="Week", calc for week, 
    ….)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Seward12533,

       

      Thank you so much for the reply. That would work. But I have one question, If user selects any of this slicer, Lets say user selects Month, then he should allow to select only 'Month filter' or if he selects Week, then he should see Week filter. Is it possible?

      Because based on the selection, they want to see past period data. If user selects 'Feb 2018', he wants to see data from Feb -2017 to Feb 2018. (Last 12 months based on selection)

      Regards,

      Poonam

      • Seward12533's avatar
        Seward12533
        Icon for Solution Sage rankSolution Sage
        8 years ago

        You would ahve to give me a better idea of what your data model and visuals look like.

         

        If you just want to limit the data range based on the choice and then use other filters to further refine its a bit more complicated. In this case you don't want to put the logic into the measures but rather use Time Intelligence and dyanmic filter context of Power BI.  The approach woudl be to write a meausure to calculate the EARLIEST_DATE and LATEST_DATE based on today's date (or selected daate) and the choice form the disconnected slicer.  Then add a calcualted column to your date table called "Include" or something like that and test to see if the date on each row of the date table is in that range or not.  Then use a Page filter to only include Include="YES".  This will imit the scope of all the calcualtions to within the date range you calculate.

         

        Hoep this helps.