Forum Discussion

Macwin's avatar
Macwin
Icon for Helper I rankHelper I
4 years ago
Solved

Calculate Amount with the same specific date range in every year

Hello, currently im trying to setup a measure for calculating a sales amount with a specific date range in every year.   For Example: Daterange -> 01.01 .. 10.31 Years -> 2017..2018..2019..2020 ...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 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 Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

Macwin Maybe:

Measure =
  VAR __MinDate = MIN('Table'[Date])
  VAR __MaxDate = MAX('Table'[Date])
  VAR __MinMonth = MONTH(__MinDate)
  VAR __MinDay = DAY(__MinDate)
  VAR __MaxMonth = MONTH(__MaxDate)
  VAR __MaxDay = DAY(__MaxDate)
  VAR __Year = MAX('Table1'[Year])
  VAR __NewMinDate = DATE(__Year,__MinMonth,__MinDay)
  VAr __NewMaxDate = DATE(__Year,__MaxMonth,__MaxDay)
RETURN
  CALCULATE(SUM('Table3'[SalesAmount]),FILTER(ALL('Table3'),[Date]>=__NewMinDate && [Date]<=__NewMaxDate))

Totally guessing on your table names and structure. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.