Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Have a fixed Date range based on selected current period lastdate

Hi Guys, I need some help. I created a model based on this article by SQLBI sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/. I would like to have a fixed previo...
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi amitchandak 

Thank you for your solution.

Is there a way the above measure can be used as a calculated column in the date table and also for it to be stand alone from the sales amount? Ideally, the previous period is to be used as a slicer with the current period to filter values for Sales Amount and Previous sales.




  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please create relationship between the data table and Sales table.

    Then create a column.

    Same Date range last period =
VAR _diff =
    DATEDIFF ( MIN ( 'Date'[date] ), MAX ( 'Date'[date] ), DAY )
VAR _p_st_date =
    MINX ( 'Date', DATEADD ( 'Date'[date], -1 * _diff, DAY ) ) - 1
VAR _p_end_date =
    MAXX ( 'Date', DATEADD ( 'Date'[date], -1 * _diff, DAY ) ) - 1
RETURN
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        FILTER (
            'Date'[date],
            'Date'[date] >= _p_st_date
                && 'Date'[date] <= _p_end_date
        )
    )

    Or

    Same Date range last period =
VAR _diff =
    DATEDIFF ( MIN ( 'Date'[date] ), MAX ( 'Date'[date] ), DAY )
VAR _p_st_date =
    MINX ( 'Date', DATEADD ( 'Date'[date], -1 * _diff, DAY ) ) - 1
VAR _p_end_date =
    MAXX ( 'Date', DATEADD ( 'Date'[date], -1 * _diff, DAY ) ) - 1
RETURN
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        DATEADD ( 'Date'[date], -1 * _diff, DAY )
    )

    If I have misunderstood your meaning, please provide you pbix file without privacy information and desired output.

     

    How to Get Your Question Answered Quickly 

     

    Best Regards

    Community Support Team _ Polly

     

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