Forum Discussion

stephanegodbout's avatar
stephanegodbout
Regular Visitor
6 years ago
Solved

Picking two dates

Hello everyone.

 

I am new to Power BI with most of my experience coming from the Oracle world -PL SQL etc.  What we are trying to do is the following:

 

1 - We need to pick the last recordset that was loaded in the database.  The field that contains the date information is "RecordDate".

2 - We need to also pick the coresponding weekday recordset from a year ago or as close to it as possible without going forward. 

3 - No data is loaded on Sathurday or Sunday.  

 

e.g.  Assume the last database load occured on 2020-02-03 (Monday) - we select that date.  We know that there was no data loaded on 2019-02-03 (Sunday) and 2019-02-02 (Sathurday) so we also include / select 2019-02-01 for comparitive purpcoses.

 

Assuming the folloiwing query

 

let
Source = Sql.Databases("xxxx\yyyy"),
zzzz = Source{[Name="zzzz"]}[Data],
dbo_BlaBla = zzzz{[Schema="dbo",Item="BlaBla"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_BlaBla, let latest = List.Max(dbo_BlaBla[RecordDate]) in each [RecordDate] = latest)
in
#"Filtered Rows"

 

Any sugesstions on how to deal with this.  Any help would be greathly appreciated.

 

Regards,

 

SCG

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi stephanegodbout 

    If you want to create reports for comparision, you could create measures using dax as below

     

    max date = CALCULATE(MAX(Sheet2[date]),ALL('Sheet2'))

max date last year =
CALCULATE (
    MAX ( Sheet2[date] ),
    FILTER (
        ALL ( Sheet2 ),
        DATEDIFF (
            Sheet2[Date],
            [max date],
            YEAR
        ) >= 1
            && ( NOT WEEKDAY([date],2)
            IN {
            6,
            7
        } )
    )
)


current value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),Sheet2[date]=[max date]))

last year value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),Sheet2[date]=[max date last year]))

     

    Best Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello stephanegodbout 

     

    try this syntax

     

    let
Source = Sql.Databases("xxxx\yyyy"),
zzzz = Source{[Name="zzzz"]}[Data],
dbo_BlaBla = zzzz{[Schema="dbo",Item="BlaBla"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_BlaBla, each [RecordDate]=List.Max(dbo_BlaBla[RecordDate]))
in
#"Filtered Rows"


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi stephanegodbout 

    If you want to create reports for comparision, you could create measures using dax as below

     

    max date = CALCULATE(MAX(Sheet2[date]),ALL('Sheet2'))

max date last year =
CALCULATE (
    MAX ( Sheet2[date] ),
    FILTER (
        ALL ( Sheet2 ),
        DATEDIFF (
            Sheet2[Date],
            [max date],
            YEAR
        ) >= 1
            && ( NOT WEEKDAY([date],2)
            IN {
            6,
            7
        } )
    )
)


current value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),Sheet2[date]=[max date]))

last year value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),Sheet2[date]=[max date last year]))

     

    Best Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.