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

[Anonymous]

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

[Seward12533]

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]

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]

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.

[Anonymous]

hI @Seward12533,

I think the problem is different. Sorry, I just realized.

Just for testing purpose I changed my calculation to calculate last 30 days value.

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

If this calculation is based on lowest aggregation level, if I select Month : July 2018, it should show me data  from Jun-2018 to july-2018. But as I have selected 'Month' filter. it is showing the data only for July month. In the below screen shot,

'OIF_value_EUR_Calc' should show value from jun-2018 to july -2018 as I changed formula to calculate last 30 days value. but at the same time I have selected 'Month' filter, it is showing the data for '1st july 2018' to '11th july-2018' only. not calculating last 30 days. If I changed the same formula to claulate last 10 days value, it calulate data only for 5 days correctly as it is in same month. (See the 2nd screen shot). Do I have to ignore 'Month/Quarter/Week' selection in this case. but at the same time, I want to show the last period data based on filter selection only. What can be done in this case.

Thank you!

[Seward12533]

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]

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]

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?

 

[Amardeep100115]

How can have PBIX file of this

[Anonymous]

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 · Accepted solution]

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]

Hi @Seward12533,

First of all Thanks for spending time to explain me.

I tried the above things which you mentioned. I couldn't do the 1st option as my data model didn't allow me to create link between 'DateDim' table and 'Filter Table' which you mentioned to create lookup Table as it gave me error cannot create the relationship as it has null data.

I tried the 2nd option, created disconnected table 'Filter_Table' using the below formula:

OIF_Value_EUR_Calc = VAR LstDate = MAX (Filter_Table[Date]) RETURN
CALCULATE (
SUM ( V_OPPORTUNITIES_OIF[OIF_Value_EUR] ),Filter_Table[Date],
FILTER(ALL(DateDIM) , DateDIM[date]<=LstDate&&DateDIM[date]<=LstDate-3))

But I am not getting one thing. For the disconnected silicer, how I would get relative 'OIF_EUR_Value_Calc' value. I tried using 'SelectedValues' but it didn't work. Am i doing anything wrong here?

Thanks!

- Poonam

[Anonymous]

Hi @Seward12533,

It is working now. I changed my formula as below:

OIF_Value_EUR_Calc = VAR LstDate = (max(Filter_Table[Date])) Return
CALCULATE (
SUM ( V_OPPORTUNITIES_OIF[OIF_Value_EUR] ),
 DateDIM[Date]<=LstDate&&DateDIM[Date]>=LstDate-365)

Thanks a lot for all your help! 🙂

Regards,

Poonam

[Seward12533]

If its a good date table then there should not be blanks 🙂   But I'm glad it worked! BTW if you ever wanted to try the option I suggested for creating the bidge table dynamically with DAX

NTHS = CALCULATE(VALUES(date[YearMonthShort]),NOT(ISBLANK(date(YearMonthyShort)))