Forum Discussion

JS00's avatar
JS00
Frequent Visitor
2 years ago
Solved

Prior Period based on Selected Date Range

Hello,

 

I made a date filter table on PowerBi that has a bi-directional relationship with my Calendar table, the Calendar table has a relationship with my fact table. 

 

The date filter table is a slicer on my report and allows users to choose stats for past 7 days, past 14 days, and past 28 days.

 

I want to create a measure so when the user chooses the for the past 7 days, it also shows them the stats for the 7 days prior to the past 7 days. (same for other date ranges). For example, if the user choose the past 7 day today, it will show them 1/24-1/30 stats then stats for 1/17-1/23.

 

Thank you!

 

 

 

 

Need Help
  • speedramps's avatar
    speedramps
    2 years ago

    JS00 
    Try this solution
    Click here to download PBIX from OneDrive 

    How it works ...

    Create relationships

     

    Create measure 

    Previous start = 
DATEVALUE(
CALCULATE(MIN(Calandar[Date]), ALL(Calandar))
     -  [Previous duration]
)

     

    Previous end = 
DATEVALUE(
CALCULATE(MIN(Calandar[Date]), ALL(Calandar))
 - 1
)



    Previous duration = 
DATEDIFF(
    CALCULATE(MIN(Calandar[Date]), ALL(Calandar)),
    CALCULATE(MAX(Calandar[Date]), ALL(Calandar)),
    DAY
    )

     

    Sales this period = 
SUM(Facts[Amount])

     

    Sales previous day = 
var previousstart = [Previous start]
var previousend = [Previous end]
RETURN
CALCULATE(
SUM(Facts[Amount]),
ALL(datefilter),
Facts[Date] >= previousstart && Facts[Date] <= previousend
)

     

    Create report

     

     

     

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    This solution works and does exactly what you asked.
    So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

    Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort.

    One question per ticket please. If you need to change or extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the [accept as solution] and the thumbs up button.  Thank you.

2 Replies

  • speedramps's avatar
    speedramps
    Super User
    2 years ago

    JS00 
    Try this solution
    Click here to download PBIX from OneDrive 

    How it works ...

    Create relationships

     

    Create measure 

    Previous start = 
DATEVALUE(
CALCULATE(MIN(Calandar[Date]), ALL(Calandar))
     -  [Previous duration]
)

     

    Previous end = 
DATEVALUE(
CALCULATE(MIN(Calandar[Date]), ALL(Calandar))
 - 1
)



    Previous duration = 
DATEDIFF(
    CALCULATE(MIN(Calandar[Date]), ALL(Calandar)),
    CALCULATE(MAX(Calandar[Date]), ALL(Calandar)),
    DAY
    )

     

    Sales this period = 
SUM(Facts[Amount])

     

    Sales previous day = 
var previousstart = [Previous start]
var previousend = [Previous end]
RETURN
CALCULATE(
SUM(Facts[Amount]),
ALL(datefilter),
Facts[Date] >= previousstart && Facts[Date] <= previousend
)

     

    Create report

     

     

     

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    This solution works and does exactly what you asked.
    So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

    Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort.

    One question per ticket please. If you need to change or extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the [accept as solution] and the thumbs up button.  Thank you.