Forum Discussion

Senya's avatar
Senya
Frequent Visitor
8 years ago
Solved

Dynamic comparison for changing time period

Hello,   At first, let me tell you about the data I'm working with. It's web data from Google Analytics for the last 6-8 weeks so it's got I've got dates, values, and lots of dimensions.Here's wha...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Senya,

     

    I wounder if the measure below is what you want. Please check out the demo in the attachment. If this isn't what you want, please point out the logic.

    Measure =
VAR selectedDays =
    CALCULATE ( COUNT ( DimDate[Datekey] ), ALLSELECTED ( DimDate[Datekey] ) )
VAR minDate =
    CALCULATE ( MIN ( 'DimDate'[Datekey] ), ALLSELECTED ( 'DimDate'[Datekey] ) )
RETURN
    IF (
        selectedDays <= 7,
        CALCULATE (
            SUM ( FactSales[SalesQuantity] ),
            DATESINPERIOD ( 'DimDate'[Datekey], minDate - 7, - selectedDays, DAY )
        ),
        CALCULATE (
            SUM ( FactSales[SalesQuantity] ),
            DATESINPERIOD ( 'DimDate'[Datekey], minDate - 1, - selectedDays, DAY )
        )
    )

    Dynamic_comparison_for_changing_time_period1

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Senya,

 

I wounder if the measure below is what you want. Please check out the demo in the attachment. If this isn't what you want, please point out the logic.

Measure =
VAR selectedDays =
    CALCULATE ( COUNT ( DimDate[Datekey] ), ALLSELECTED ( DimDate[Datekey] ) )
VAR minDate =
    CALCULATE ( MIN ( 'DimDate'[Datekey] ), ALLSELECTED ( 'DimDate'[Datekey] ) )
RETURN
    IF (
        selectedDays <= 7,
        CALCULATE (
            SUM ( FactSales[SalesQuantity] ),
            DATESINPERIOD ( 'DimDate'[Datekey], minDate - 7, - selectedDays, DAY )
        ),
        CALCULATE (
            SUM ( FactSales[SalesQuantity] ),
            DATESINPERIOD ( 'DimDate'[Datekey], minDate - 1, - selectedDays, DAY )
        )
    )

Dynamic_comparison_for_changing_time_period1

 

Best Regards,

Dale