cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vishy86
Post Patron
Post Patron

Assign Slicer Value based on values part of another column

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

2 REPLIES 2
mwimberger
Resolver II
Resolver II

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

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors