cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Summarizing columns from three different date ranges(Please let me know if this the correct approah)

I have created a report that would filter based on three date ranges as below

 

 

 

DateFilterRange = IF(MAX(vw_patientListNew[StatusDate]) in UNION(VALUES(DateRange1[Date]),VALUES(DateRange2[Date]),VALUES(DateRange3[Date])),1)

 

 

 

roopesh_0-1596026790732.png

Now i want to summarize it like the table below

 TimeFrameDateRangeCol1TimeFrameDateRangeCol2TimeFrameDateRangeCol3
Interventional TX236512
Interventional Non TX13419

 

 

 

TimeFrameDateRangeCol1 = 
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRange1[Date]),1)
TimeFrameDateRangeCol2 = 
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRang2[Date]),1)
TimeFrameDateRangeCol3 = 
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRange3[Date]),1)

 

 

TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3  are calculation column and should return 1 when the statusdate intersect the respective filter. As you can see i am not able to achieve with the Daxcode.

 

Once i get the values for the each collumn TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3 i can summarize TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3 to create a summary table.

 

Is there another solution to this? 

 

12 REPLIES 12
Highlighted
Super User IV
Super User IV

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

@roopesh , three slicers should be coming from three date tables or three-column. You might have ignore the other when one is working.

 

refer to this example for two date ranges

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

@roopesh not sure how tables are connected like slicers are from disconnected tables, you have to add your calculation as measures, not columns

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper IV
Helper IV

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

 

Spoiler
@amitchandak  Yes the slicers are coming from three different date tables, exactly as you mention in the article link.
roopesh_0-1596028670335.png

 As you can see above the slicer data is coming from three different tables.

 

Highlighted
Solution Sage
Solution Sage

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

Hi @roopesh,

 

Are you able to provide some sample data and what the expected outcome would be. One thing to note is that calcuated columns do not evaluate in the context of any filters or slicers applied to the report, they are calcluated when the model is processed, the values are then locked in place. Measures on the other hand are evaluated when the model is queried by the report.

 

Hope that makes sense

Highlighted
Helper IV
Helper IV

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

roopesh_0-1596028806043.png

These are the tables from where slicer data is coming. 

I have added the calculation measure and i cannot summarize the data. I can only summarize the data with a column.

My goal here is to create a summarized table as shown in my post.

Highlighted
Solution Sage
Solution Sage

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

Hi @roopesh,

 

If you can provide the .pbix file I am pretty sure that @parry2k , @amitchandak  or myself can help you with the measure you are looking for.

 

Thanks,

 

Richard 

Highlighted
Helper IV
Helper IV

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

The site doesn't let me upload file.

 

Data:

StatusDateInterventionType
7/21/1989 0:00Interventional TX
1/8/1990 0:00Interventional TX
4/30/1990 0:00Interventional TX
11/3/2006 0:00Interventional Non TX
11/6/2006 0:00Interventional TX
11/8/2006 0:00Interventional Non TX
11/9/2006 0:00Interventional TX
11/13/2006 0:00Interventional TX
11/14/2006 0:00Interventional Non TX
11/14/2006 0:00Interventional TX
11/15/2006 0:00Interventional Non TX
11/16/2006 0:00Interventional TX
11/17/2006 0:00Interventional Non TX
11/20/2006 0:00Interventional TX
11/22/2006 0:00Interventional Non TX
11/22/2006 0:00Interventional TX
11/27/2006 0:00Interventional TX
11/28/2006 0:00Interventional Non TX
11/28/2006 0:00Interventional TX
11/30/2006 0:00Interventional Non TX
11/30/2006 0:00Interventional TX
12/1/2006 0:00Interventional Non TX
12/1/2006 0:00Interventional TX
12/4/2006 0:00Interventional TX
12/5/2006 0:00Interventional TX
12/6/2006 0:00Interventional TX
12/7/2006 0:00Interventional TX
12/11/2006 0:00Interventional Non TX
12/11/2006 0:00Interventional TX
12/12/2006 0:00Interventional Non TX
12/12/2006 0:00Interventional TX
12/13/2006 0:00Interventional Non TX
12/14/2006 0:00Interventional TX
12/15/2006 0:00Interventional Non TX
12/18/2006 0:00Interventional Non TX
12/19/2006 0:00Interventional Non TX
12/19/2006 0:00Interventional TX
12/20/2006 0:00Interventional Non TX
12/20/2006 0:00Interventional TX
12/21/2006 0:00Interventional Non TX
12/21/2006 0:00Interventional TX
12/22/2006 0:00Interventional Non TX
12/27/2006 0:00Interventional TX
12/28/2006 0:00Interventional TX
1/2/2007 0:00Interventional Non TX
1/4/2007 0:00Interventional TX
1/5/2007 0:00Interventional Non TX
2/24/2020 0:00Interventional Non TX
2/24/2020 0:00Interventional TX
2/25/2020 0:00Interventional Non TX
2/25/2020 0:00Interventional TX
2/26/2020 0:00Interventional Non TX
2/26/2020 0:00Interventional TX
2/27/2020 0:00Interventional Non TX
2/27/2020 0:00Interventional TX
2/28/2020 0:00Interventional Non TX
2/28/2020 0:00Interventional TX
3/1/2020 0:00Interventional Non TX

 

Desired Results :

 Slicer1(1/1/1989 - 7/26/1990)Slicer2(11/3/2006 - 1/5/2007)Slicer3(2/24/2020-3/1/2020)
Interventional TX3245
Interventional Non TX0206

 

I have three slicers: 

Highlighted
Solution Sage
Solution Sage

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

Hi @roopesh 

 

Please have a look at the .pbix file created.

 

sample.pbix 

 

I contains 4 measures:

 

Slicer 1 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 1'[Date]))
Slicer 2 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 2'[Date]))
Slicer 3 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 3'[Date]))
Total Slicer Value Selection = [Slicer 1 Value] + [Slicer 2 Value] + [Slicer 3 Value]

 

which evaluate to the values you are looking for

 

richbenmintz_0-1596031942734.png

 

Hope this helps

 

Highlighted
Helper IV
Helper IV

Re: Summarizing columns from three different date ranges(Please let me know if this the correct appr

@richbenmintz Yes this the result I was expecting but when I click on the slicer 2 value and "Interventional Non TX" which has 7 counts should only display 7 but display the entire row count 13. Is there a way to display only 7 in detail report? should I use a matrix ? please suggest

 

roopesh_0-1596032912233.png

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors