Yesterday or day before

[Malinpetersson]

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

[v-frfei-msft]

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.

[az38]

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]

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]

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.

[Malinpetersson]

Hi,

how do I add the information?

 I have an list in excel whit data from mars 2019 and some date from mars 2020. We do the invocing every monday-friday and when I look to the rapport at monday I want the rapport to show fridays amount. When I look at the rapport at thusesday I want it to show mondays invoicing.

thanks 
Malin