Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Get latest records between two dates

Hi everybody,

 first of all thank you for your support in helping people and me in getting better in PowerBI.

I tried to search for similar posts but can't figure out how to solve this.

I'm building a report in which I need the latest values for each Store in which the start date is the beginning of the activity I'm tracking, and the end date is the Sunday of a given week number.

 

I've built a table with the matrix of the weeks and the period of competence of that week

 

 

 

YearWeekWeekFirst day dateLast day date
2022.525201/01/190001/01/2023
2023.010101/01/190008/01/2023
2023.020201/01/190015/01/2023
2023.030301/01/190022/01/2023
2023.040401/01/190029/01/2023
2023.050501/01/190005/02/2023
2023.060601/01/190012/02/2023
2023.070701/01/190019/02/2023
…………

 

This is the dataset of the records:

 

StoreValueDate
ST0111105/04/2023
ST0211205/04/2023
ST0311306/04/2023
ST0111412/04/2023
ST0211519/04/2023
ST0311626/04/2023
ST0111726/04/2023
ST0211827/04/2023
ST0311928/04/2023

 

I need to add a filter by Yearweek in which:

If I select Yearweek 2023.14 the dataset shows me the records for each store that are between the First day date and the Last day date

StoreValueData
ST0111105/04/2023
ST0211205/04/2023
ST0311306/04/2023

 

and so on for 2023.15

StoreValueData
ST0111412/04/2023
ST0211205/04/2023
ST0311306/04/2023

 

2023.16

StoreValueData
ST0111412/04/2023
ST0211519/04/2023
ST0311306/04/2023

 

and 2023.17

StoreValueData
ST0111726/04/2023
ST0211827/04/2023
ST0311928/04/2023

 

please note that in the dataset for store ST03 there are 2 records that belong to week 2023.17 (one in date 26/04/2023, the other one 28/04/2023). The filter should take into consideration the latest in the period, so 28/04/2023.

 

I have no clues to do this in this specific dynamic way.

Hope you can help me

Thank you!

Need Help
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

     

    Value measure: = 
VAR _latestwkdate =
    MAX ( 'Calendar'[Wkenddate] )
VAR _latestdatadate =
    MAXX ( FILTER ( Data, Data[Date] <= _latestwkdate ), Data[Date] )
VAR _result =
    CALCULATE ( SUM ( Data[Value] ), Data[Date] = _latestdatadate )
RETURN
    IF ( HASONEVALUE ( Store[Store] ), _result )

     

    Date measure: =
VAR _latestwkdate =
    MAX ( 'Calendar'[Wkenddate] )
VAR _latestdatadate =
    MAXX ( FILTER ( Data, Data[Date] <= _latestwkdate ), Data[Date] )
RETURN
    IF ( HASONEVALUE ( Store[Store] ), _latestdatadate )

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

     

    Value measure: = 
VAR _latestwkdate =
    MAX ( 'Calendar'[Wkenddate] )
VAR _latestdatadate =
    MAXX ( FILTER ( Data, Data[Date] <= _latestwkdate ), Data[Date] )
VAR _result =
    CALCULATE ( SUM ( Data[Value] ), Data[Date] = _latestdatadate )
RETURN
    IF ( HASONEVALUE ( Store[Store] ), _result )

     

    Date measure: =
VAR _latestwkdate =
    MAX ( 'Calendar'[Wkenddate] )
VAR _latestdatadate =
    MAXX ( FILTER ( Data, Data[Date] <= _latestwkdate ), Data[Date] )
RETURN
    IF ( HASONEVALUE ( Store[Store] ), _latestdatadate )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Kim,

      Thank you very much for the descriptions, screenshots and PBIX example! 🙏

      This is EXACTLY what I was looking for! You gave me a big hand in this!

      Now I just need to link this behaviour to some measures I've created in order to take just the data related to the latest "Value Measure" shown in the filter...

       

      Thanks again!

      Have a nice day!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi again Kim,

     sorry if I return on this but can't make it further.

    Unfortunately getting the latest Value with a dedicated measure is not enough for my purposes, as I need to analyze answers to some questions just for the latest Value in the filter that all belong to Data table.

     

    This is the "extended" dataset:

     

    StoreValueDateQuestion 1Question 2Question 3Question 4
    ST0111105/04/2023YesYesYesYes
    ST0111412/04/2023NoYesYesYes
    ST0111726/04/2023YesYesYesYes
    ST0211205/04/2023YesYesYesYes
    ST0211519/04/2023YesNoYesYes
    ST0211827/04/2023YesYesYesYes
    ST0311306/04/2023YesYesYesYes
    ST0311626/04/2023YesNoYesYes
    ST0311928/04/2023YesYesYesYes

     

    I think I have asked you support without considering the fact that all the analysis that follow must be taken just for the rows that have the latest Value measure: that comes from the YearWk filter.

     

    I also need to obtain with this filter is to calculate a Compliance column that gives me "Yes" if all the questions 1, 2, 3 and 4 are "Yes". If at least one of the Questions is "No", the measure should give me "No" as a result.

    The column should be quite easy to do:

     

    Compliance = IF(Data[Question 1]="Yes" && Data[Question 2]="Yes" && Data[Question 3]="Yes" && Data[Question 4]="Yes" ,"Yes","No")
     
    If I create a pie chart with the Compliance column in it, it takes all the values of the dataset (9 rows in the example) and not just the result of the Compliance for just the Value measure: result that comes with your suggested solution.
     

     

     

     

     

    If I link the Data[Date] and Calendar[Date] together, it just gives me the results of the rows that belong to that week and not the latest results for each store that are between Wkstartdate and Wkenddate

     

    2023.14

     

    2023.15

     

    2023.16

     

    2023.17

     

    Completely wrong 😭

     

    At Table Visual point of view also this is what happens.

    When I add these to the table and filter to 2023.14, everything looks fine

     

    StoreDate measure:Value measure:Question 1Question 2Question 3Question 4
    ST0105/04/2023111YesYesYesYes
    ST0205/04/2023112YesYesYesYes
    ST0306/04/2023113YesYesYesYes

     

    If I filter to 2023.15, the row with Value measure: 111 is still there, but it should not be there as I need just to take the latest one, so it should show just the Value measure: 114 for ST01

     

    StoreDate measure:Value measure:Question 1Question 2Question 3Question 4
    ST0112/04/2023114NoYesYesYes
    ST0105/04/2023111YesYesYesYes
    ST0205/04/2023112YesYesYesYes
    ST0306/04/2023113YesYesYesYes

     

    Maybe it's correct from a Table Visual point of view because the Question 1 is different between Value 114 and Value 111.

    It gets more complicated when filtering 2023.16. 

     

    StoreDate measure:Value measure:Question 1Question 2Question 3Question 4
    ST0112/04/2023114NoYesYesYes
    ST0105/04/2023111YesYesYesYes
    ST0219/04/2023115YesNoYesYes
    ST0205/04/2023112YesYesYesYes
    ST0306/04/2023113YesYesYesYes

     

    Values 111 and 112 should not be there. But also here the Question 1 is different between Value 114 and Value 111 and Question 2 is different between Value 115 and Value 112.

     

    What I need to obtain is something like this:

     

    2023.14

     

    2023.15

     

    2023.16

     

    2023.17

     

    I hope my goal is clearer now. Maybe the solution is easier than I can expect but honestly it's been 3 days I'm beating my head against a brick wall without finding a satisfying solution.

     

    Hope you can help with this!

    I attach the pbix as well

    https://drive.google.com/file/d/116C9igi6QuQMwudVnlIZr_-IW_zRw0Ne/view?usp=share_link

     

    Thank you so much in advance for your great support!