Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Last 4 weeks Revenue

Hello,

 

I have a Date Table and Fact Table

Date and Fact connected by Date.day field

I need to get SUM(Revenue) from fact for Last 4 weeks.

 

Can you please help?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous,

    You can use the current date as condition to calculate the previous date and use it to filter table to aggregate 4-week records:

    formula =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Revenue] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                >= DATE ( YEAR ( currdate ), MONTH ( currdate ), DAY ( currDate ) - 28 )
                && [Date] <= currDate
        ),
        VALUE ( Table[Category] )
    )

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous,

    You can use the current date as condition to calculate the previous date and use it to filter table to aggregate 4-week records:

    formula =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Revenue] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                >= DATE ( YEAR ( currdate ), MONTH ( currdate ), DAY ( currDate ) - 28 )
                && [Date] <= currDate
        ),
        VALUE ( Table[Category] )
    )

    Regards,

    Xiaoxin Sheng