Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ppz
Regular Visitor

number of days from slicer

I'm trying to get the number of days to calculate the average over a period of time selected by a slicer.

Let's say i select Last week in the slicer, then i want the number to be 7 days and then i can use that to calculate.

if i chose last month it should return the number of days in the last month and so on..

 

But i cant seem to get a "constant" value from my datetable.

this is how i create my date table

DateTable = CALENDARAUTO()

 

I've tried firsdate/lastdate datediff

allselected

datesbetween

counting rows..

 

it's probably a simple fix but i cant seem to figure this one out

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @ppz,

 

>>Let's say i select Last week in the slicer, then i want the number to be 7 days and then i can use that to calculate.

if i chose last month it should return the number of days in the last month and so on.

Maybe you can try to add a calculated column to calendar table to mark data range, then use this column as the source of slicer. After these steps, you can get the range count based on slicer.

 

In addition, you can also take a look at below article about use two slicers to filter on different date unit.

Calculate Dynamic Rolloing Total Based on Slicer

 

BTW, current power bi not support to analysis specific date range based on text, you need to manually build a mapping table to achieve these operation.

 

Regards

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft , could you please explain what kind of calculated column you should add to the Calendar Table? 
My slicer is associated to the "CurrentDay" date in my Calendar table.

 

Then I added this column:
TotDays = calculate(count('Calendar'[CurrentDay]))

 
but it returns the total number of days in the whole Calendar table, no matter what range of dates I select in the slicer...
 
Thanks for your help!
Bye,
A.-

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.