Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 previous date slider based on the selection of current period date slider for example in the article the selected current period is 8/1/2009 to 8/31/2009. In my case, I would like the previous period to be from the Last date of current Period (8/31/2009) to a start date within the same dateperiod as selected in the current (7/1/2009).

Therefore, if selected current period is two months then previous should be 2 months from the last date in date slider. 

Here's a screenshot of the before 

md_1234_0-1654222695846.png


After 

md_1234_1-1654223001601.png

 

 

4 REPLIES 4
Anonymous
Not applicable

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

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.

Anonymous
Not applicable

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
Not applicable

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.

 

amitchandak
Super User
Super User

@Anonymous ,

You can initialize like that

 

You can however create a measure

 

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]),all('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)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.