Forum Discussion
Last period Data based on Date/Quarter/Month/Week Filter selection
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.
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
Solution SageIts 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.
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
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)))