Picking two dates

[stephanegodbout]

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

[v-juanli-msft · Accepted solution]

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.

[Jimmy801]

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