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 ...
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. 

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

Thanks, that helps me understand what your trying to do.   First for this to work correclty you need a date table and your Slicer has to filter your data table and NOT your DATA table (also if you build visuals you need to use the date fields (month/quarter/year etc) from your data table as well as rows/columns/axis on your visuals.

 

Assuming you have a date table try this where dimdate is your data table and dimdate[Date] is the primary key from your date table and is related to your V_OPPORTUNITIES_PERIOD[Created_Date] in the model

 

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

 

Note, I have not used DATESINPERIOD before as I tend to use a more generic DAX pattern which can be handy if you want to do things like cumulative since the beginning of time.  

CALCULATE(original measure,

          Custom Calendar Table,  // All not needed

          FILTER(ALL(Custom Calendar Table), 

                 logic to select a modified date range)

 

So in your situation

 

OIF_Value_EUR_Calc =VAR LastDate = MAX (V_OPPORTUNITIES_PERIOD[Created_Date]) RETURN
CALCULATE (
SUM ( V_OPPORTUNITIES_OIF[OIF_Value_EUR] ),dimdate,
FILTER(ALL(dimdate[Date]) , dimdate[date]<=LastDate&&dimdate[date]<=LastDate-30))

 

 

 

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

Also if you want to sort your months use the sortby column from the modeling tab in the table view and sort the Month name by Month Index.  As good date table is essential there are many articles on this if you search but here is some DAX to create one dynamically if you don't have a good one already built.  It also includes examples of some custom date fields my company uses based on our Fiscal Years (starts 4/1/1962)

 

DateDIM = 
ADDCOLUMNS (
CALENDAR (DATE(year(today())-2,1,1), DATE(year(TODAY()),12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY-MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY-mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ),
"TELQuarter", switch(format([Date],"Q"),"1","4","2","1","3","2","4","1"),
"TELYear", year([Date])-1962-if(format([Date],"Q")="1",1,0),
"TELYearMonthNum",year([Date])-1962-if(format([Date],"Q")="1",1,0)&"-"&format([Date],"MM"),
"TELYearMonthShort",year([Date])-1962-if(format([Date],"Q")="1",1,0)&"-"&format([Date],"mmm"),
"TELYearQuarter", year([Date])-1962-if(format([Date],"Q")="1",1,0) & "Q" & switch(format([Date],"Q"),"1","4","2","1","3","2","4","1"),
"TELYearHalf", year([Date])-1962-if(format([Date],"Q")="1",1,0) & "H" & switch(format([Date],"Q"),"1","2","2","1","3","1","4","2")
)
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Seward12533,

     

    Thanks you so much for your reply. I got the logic now. I follow all the steps which you mentioned.

     

    1) Created Calendar Table. (Used your Calendar script only)

    2) Changed the calculated field as follows: (For testing purpose calculating last 3 days value)

     

    OIF_Value_EUR_Calc = VAR LastDt = MAX (V_OPPORTUNITIES[Created_Date]) RETURN
    CALCULATE (
    SUM ( V_OPPORTUNITIES_OIF[OIF_Value_EUR] ),DateDIM,
    FILTER(ALL(DateDIM[Date]) , DateDIM[Date]<=LastDt&&DateDIM[Date]>=LastDt-3))

     

    But when I select MonthYear as 'Feb2-2018', The calculated filed not showing me last 3 months value.

    'OIF_Value_EUR' and 'OIF_Value_EUR_Calc' showing me same value. Am I missing anything? Could you please help?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Seward12533,

       

      Sorry about the above message. Actually the below formula is working fine when I calculate last 3 days value when filtered 'Feb-2018' data. (I had selected Date filter so it was showing same values before)

       

      OIF_Value_EUR_Calc = VAR LastDt = MAX (V_OPPORTUNITIES[Created_Date]) RETURN
      CALCULATE (
      SUM ( V_OPPORTUNITIES_OIF[OIF_Value_EUR] ),DateDIM,
      FILTER(ALL(DateDIM[Date],DateDIM[YearMonthShort]) , DateDIM[Date]<=LastDt&&DateDIM[Date]>LastDt-3))

       

      But when I changed the formula to calulate last 60 days value, I am getting same numbers for both the column for 'Feb-2018' filter. The 'OIF_Value_EUR_Calc' filed should have shown Jan and Feb 2018 values. I tried adding 'YearMonthShort' field in the formula to filter  that field but didn't work. Can you please help on that. Thank you!

       

      - Poonam

      • Seward12533's avatar
        Seward12533
        Icon for Solution Sage rankSolution Sage
        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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      How can have PBIX file of this