Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Hope that somone can help me.
When i use date dimension i works but only for dates (Dim Avslut Date)
I have created a date+hour dimension (Dim Avlut DateHour)
The problem to summerize is the show to totalt parking hour by hour for the last 24 hours.
On every hour information is stored about ended parking for different parking devices,
on different areas and how long time they have parked.
(So there is a need to summerice this 24 hour slices for every hour to see the Parking Usage - compair to rolling 12 month)
Using date working fine for the measur but not for date+hour
UsageDate =
VAR DSTART=FIRSTDATE(DATEADD('Dim Avslut Date'[FullDateKey];-7;DAY))
VAR DEND=LASTDATE(DATEADD('Dim Avslut Date'[FullDateKey];0;DAY))
RETURN
CALCULATE ( SUM('Fact Besoksparkering_BA_TP'[Antal_Parkering]) ;
DATESBETWEEN('Dim Avslut Date'[FullDateKey];DSTART;DEND))
If I understand right, below function support date/time
-FIRSTDATE
-LASTDATE
-DATESBETWEEN
What to us instead of DATEADD
I have create a calculated column white the starting boundary
'Fact Besoksparkering_BA_TP'[KEY_START_DATE_HOUR] -calculated
'Fact Besoksparkering_BA_TP'[KEY_DATE_HOUR]
UsageDate24Hour =
VAR DSTART=?? KEY_START_DATE_HOUR
VAR DEND=?? KEY_DATE_HOUR
RETURN
CALCULATE ( SUM('Fact Besoksparkering_BA_TP'[Antal_Parkering]) ;
DATESBETWEEN('Dim Avslut Date'[FullDateKey];DSTART;DEND))
This is end of task 1.
The next task is also add a filter to just take summerize how long time they have parked.
I have a dimension with different parking intervalls 1 hour, 2 hours and so on
If you are able calculate the 24 slices you can use this calculation to select the right interval.
((DEND-KEY_DATE_HOUR (first one)) * 24) +1 = 1 Summerize intervall >=1
((DEND-KEY_DATE_HOUR (1 hour before - )) * 24) +1 = 2 Summerize intervall >=2
..
((DEND-KEY_DATE_HOUR (23 hour before - )) * 24) +1 = 24 Summerize intervall >=24
KEY_DATE_HOUR (23 hour before) = KEY_START_DATE_HOUR
How can I set up this filter
//Lars
Correction
UsageDate24Hour =
VAR DSTART=?? KEY_START_DATE_HOUR
VAR DEND=?? KEY_DATE_HOUR
RETURN
CALCULATE ( SUM('Fact Besoksparkering_BA_TP'[Antal_Parkering]) ;
DATESBETWEEN('Dim Avslut DateHour'[FullDateKey];DSTART;DEND))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |