Assign Slicer Value based on values part of another column

[vishy86]

Hi,

This is a follow-up to the original post which was on difference value between 2 cumulative measures for the dates selected by end user. The link is as below.

https://community.powerbi.com/t5/Desktop/Difference-between-two-Measures-with-Cumulative-Totals/m-p/...

The sample pbix file is also uploaded on Google Drive, the link for which is below. The final name is "Difference Report - Sample Data - way2".

https://drive.google.com/open?id=1xwmH74Gejc-6g53xuKbb3haXnhnntdks

I have a question on the user selected dates in way2 since I have used that logic in my report. The slicers should only show the CORP_PERIOD_DT values for selection as only the data for those specific CORP_PERIOD_DTs are loaded. In way1, we only see CORP_PERIOD_DT for data loaded which is Dec 2018 and Dec 2017 which is right.

But in way2, we see many dates as we are creating a Date table using CALENDARAUTO() and PreviousDate. 

How do I accomplish the same slicer values for selection as with way1 i.e. only CORP_PERIOD_DTs for which data is loaded should be available to the end user for selection.

Maybe, the CALENDARAUTO() function will have to be changes, not sure and not able to find a solution.

Please help with this.

Thanks,

Vishy

[mwimberger]

Hi @vishy86 

I'm not entirely sure what you are trying to do with the two date tables. There is an inative relationship between them which might be why you were having problems ( if you go to the relationships view this is indicated by the dotted line)

I think a better way to generate a date table is to use the minimum date from your data table. I've tried to quickly recreate something for you ( not sure of the differenece between DT1 and DT2) but having the min of both of these is a good start for the calendar.

You can use this table creation dax to create a date table with the relevant columns. Remember that the slicers should be linked to the date calendar table ( so slicers to dimensions and not fact tables) :

date = 
Var DateDT1 = Min(Sheet1[CORP_PERIOD_DT 1])
Var DateDT2 = Min(Sheet1[CORP_PERIOD_DT 2])
Var MinDT1DT2 = Min(DateDT1,DateDT2)
Return


addcolumns(
filter(
calendarauto(),
 ([Date])>= MinDT1DT2
),
"Year", Year([date]),
"Month", format([date],"mmmm"),
"Year Month Text", Year([date]) & Month([Date]),
"Month Number", Month([Date]),
"Reporting Period", Year([Date]) & FORMAT ( Month([Date]), "00" ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Date as MMMM", Format([Date],"MMMMYYYY"),
"HalfYear",YEAR([Date])&" HY "&ROUNDUP(MONTH([Date])/6,0),
"Quarter",YEAR([Date])&" QTR "&ROUNDUP(MONTH([Date])/3,0),
"YearMonth Number 3", COMBINEVALUES("-",(format([date],"yyyy")),(Format([date],"mmmm"))),
"Year Month 1",   format([date],"mmmm yyyy"),
"Year Month 2", COMBINEVALUES("-",(format([date],"yy")),(Format([date],"mm"))),
"YearMonth Number", CONCATENATE((format([date],"yyyy")),(Format([date],"mm"))),
"YearMonth Number 2", COMBINEVALUES("-",(format([date],"yyyy")),(Format([date],"mm"))
))

Let me know if this helps

Cheers

Manfred

[vishy86]

Hi Manfred,

Thanks for your response.

I have a requirement for a report wherein the user will select 2 dates indicated by the CORP_PERIOD_DATE (both the slicers will have the same column CORP_PERIOD_DATE, which the user will select). Based on the selected dates, the cumulative difference needs to be displayed.

Eg: If user selects Dec 2019 and Jan 2020 as the 2 dates, then the

Difference = Cumulative value as of Jan 2020 - Cumulative Value as of Dec 2019.

For this purpose, I was advised to create those 2 date tables as you can see in link to the previous post.

My query is in the first CORP_PERIOD_DATE slicer, I only see those periods for which data is loaded and rightly so. However, for the second slicer, I see all the periods which is not right. I want the second slicer to also show only those periods for which data is loaded.

Regards,

Vishy