Forum Discussion

Malinpetersson's avatar
Malinpetersson
Regular Visitor
6 years ago

Yesterday or day before

Hi!

 

I need a mesure that brings the value back if ther is a value yesterday and if not it will bring the value from the day before yesterday, or before that day, last year. The value I am looking for comes from invoicing and we dont make invoices at the weekend.

(CALCULATE([Summa Fakturerat];FILTER(Calender;Calender[Date] = DATEADD(Calender[Date];-1;DAY)))), I need some kind of IF ?
 
Thanks in advanced!
 
BR
 
Malin

 

 

Need Help

5 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Malinpetersson 

    it is not Power QUery question, as I understand. Better use DAX or Desktop forum sections

    I do not clearly understand what your desired result but maybe you should try a measure like 

    Measure = 
var _lastDayWithData = CALCULATE(MAX(Table[Date]); NOT(ISBLANK(Table[Summa Fakturerat])) )

RETURN

CALCULATE(SUM(Table[Summa Fakturerat]);  Table[Date]=_lastDayWithData )

    for better response please provide data example and desired output

    • Malinpetersson's avatar
      Malinpetersson
      Regular Visitor
      6 years ago

      Hi!

       

      (CALCULATE([Summa Fakturerat];FILTER(Calender;Calender[Date] = EDATE(TODAY();-12)-1)))

      (Summa Fakturerat) = my source to the information concerning the invoice.

       

      I would like my report to update everyday whit the value form last year. Last year in mars, Saturday and Sunday was 09-03-2019 and 10-03-2019. I do not have any value in my basis. I would like Power Bi to show the value från 08-03-2019 at Monday this year and the same value on Thursday this year and on Wednesday it should be 11-03-2019 as you can see below.

       

      Date                      Amount

      08-03-2019       200000

      11-03-2019      300000

       

       

      I would like to compare the invoicing yesterday, whit the budget and last year same period.

       

      This year          Budget              Last year same period

       

      245 MSEK       131 MSEK            338  MSEK

       

      I’m a rookie at Power bi. 😊

       

       

      Thanks in advanced!

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

        Hi Malinpetersson ,

         

        Sorry I cannot get you, could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

         

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

    Hi Malinpetersson ,

     

    Does that meet your requirement?

     

    Measure =
VAR cd =
    MAX ( 'Table'[Date] )
VAR previousdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < cd )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = previousdate )
    )

     

     

    Pbix as attached.