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 th...
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.

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