Forum Discussion
Calculate Amount with the same specific date range in every year
- Anonymous4 years ago
Hi Macwin
Power BI doesn't support you to show dates in dd/mm format in Between in slicer. Here I suggest you to create two unrelated dax table to create two slicer for MonthDay range.
RangeStart = VAR _LeapYear = ADDCOLUMNS ( CALENDAR ( DATE ( 2016, 01, 01 ), DATE ( 2018, 12, 31 ) ), "Month", MONTH ( [Date] ), "Day", DAY ( [Date] ), "Month/Day", FORMAT ( [Date], "mm/dd" ), "MonthDay", MONTH ( [Date] ) * 100 + DAY ( [Date] ) ) RETURN SUMMARIZE ( _LeapYear, [Month], [Day], [Month/Day], [MonthDay] )RangeEnd = VAR _LeapYear = ADDCOLUMNS ( CALENDAR ( DATE ( 2016, 01, 01 ), DATE ( 2018, 12, 31 ) ), "Month", MONTH ( [Date] ), "Day", DAY ( [Date] ), "Month/Day", FORMAT ( [Date], "mm/dd" ), "MonthDay", MONTH ( [Date] ) * 100 + DAY ( [Date] ) ) RETURN SUMMARIZE ( _LeapYear, [Month], [Day], [Month/Day], [MonthDay] )Here I create a DimDate table has relationship with Data table.
DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Quarter",QUARTER([Date]),"Month",MONTH([Date]),"Day",DAY([Date]),"MonthDay",MONTH([Date])*100+DAY([Date]))Measure:
QTR BY MONTHDAY RANGE = VAR _STARTMONTHDAY = SELECTEDVALUE ( RangeStart[MonthDay] ) VAR _ENDMONTHDAY = SELECTEDVALUE ( RangeEnd[MonthDay] ) RETURN CALCULATE ( SUM ( 'Table'[Qtr] ), FILTER ( DimDate, DimDate[MonthDay] >= _STARTMONTHDAY && DimDate[MonthDay] <= _ENDMONTHDAY ) )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Macwin
Power BI doesn't support you to show dates in dd/mm format in Between in slicer. Here I suggest you to create two unrelated dax table to create two slicer for MonthDay range.
RangeStart =
VAR _LeapYear =
ADDCOLUMNS (
CALENDAR ( DATE ( 2016, 01, 01 ), DATE ( 2018, 12, 31 ) ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"Month/Day", FORMAT ( [Date], "mm/dd" ),
"MonthDay",
MONTH ( [Date] ) * 100
+ DAY ( [Date] )
)
RETURN
SUMMARIZE ( _LeapYear, [Month], [Day], [Month/Day], [MonthDay] )RangeEnd =
VAR _LeapYear =
ADDCOLUMNS (
CALENDAR ( DATE ( 2016, 01, 01 ), DATE ( 2018, 12, 31 ) ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"Month/Day", FORMAT ( [Date], "mm/dd" ),
"MonthDay",
MONTH ( [Date] ) * 100
+ DAY ( [Date] )
)
RETURN
SUMMARIZE ( _LeapYear, [Month], [Day], [Month/Day], [MonthDay] )
Here I create a DimDate table has relationship with Data table.
DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Quarter",QUARTER([Date]),"Month",MONTH([Date]),"Day",DAY([Date]),"MonthDay",MONTH([Date])*100+DAY([Date]))
Measure:
QTR BY MONTHDAY RANGE =
VAR _STARTMONTHDAY =
SELECTEDVALUE ( RangeStart[MonthDay] )
VAR _ENDMONTHDAY =
SELECTEDVALUE ( RangeEnd[MonthDay] )
RETURN
CALCULATE (
SUM ( 'Table'[Qtr] ),
FILTER (
DimDate,
DimDate[MonthDay] >= _STARTMONTHDAY
&& DimDate[MonthDay] <= _ENDMONTHDAY
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.