Measure that takes as a previous day, only the days where there are sales.

[Anonymous]

Hi all,

I need a car that shows me the value of the previous day for sales. I already have a calendar table, however, in the original data model, there are no sales for every day. Therefore, if I use the previous day formula and there is no sales for that day, the card goes blank. I need a measure that takes as a previous day, only the days where there are sales.

Thanks a lot

[Anonymous]

Hi @Anonymous ,

Could you please provide some sample data(exclude sensitive data) and formula which you used? The required measure is to calculate the number of days which that date have sales?

Best Regards

Rena

[Anonymous]

Hi @Anonymous, as you can see below, this an example. What I need is the sales for the previous day. For example, if I filter for 2/1/2020 I need the card to indicate the sales for the previous day (24/12/2019). But when I use PrevDay=CALCULATE(sum('Data'[Sales]);PREVIOUSDAY(Calendar[Date].[Date])) it will indicate the value for the previous day regardless of whether or not there are sales.

Date	Sales
24/12/2019	100,000
2/1/2020	600,950
5/1/2020	789,040
6/1/2020	70,000
7/1/2020	67,849
12/1/2020	494,990

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create one measure as below:

Previous Sale = var latestDate=MAX('Sales'[Date])
var previousDate=CALCULATE(max('Sales'[Date]),'Sales'[Date]<latestDate)
return CALCULATE(MAX(Sales[Sales]),'Sales'[Date]=previousDate)

Best Regards

Rena

[Greg_Deckler]

I dont need a car, but an extra one never hurts... 🙂

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amitchandak]

Try like

Measure =
var _max = maxx(allselected(Date),Date[DAte]) // or take Today()
//OR var _max = maxx(allselected(Sale),Sales[DAte]) // or take Today()

var _max2 = MAxx(filter(sales,sales[Date]<_max),sales[Date])

return
calculate(sum(sales[value]),Date[Date] =_max2)

Make sure you have date calendar

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Hi @amitchandak formula is not working.

Prueba VT$ día anterior = var _max = maxx(allselected(Calendario);Calendario[Fecha].[Date]) // or take Today() // OR

var _max = maxx(allselected('Modelo General');'Modelo General'[Fecha de Operación]) // or take Today()

var _max2 = MAxx(filter('Modelo General';'Modelo General'[Fecha de Operación]<_max);'Modelo General'[Fecha de Operación])

return

calculate(sum('Modelo General'[Valor Transado Dolarizado]);Calendario[Fecha] =_max2)

[amitchandak]

@Anonymous , please find attached solutio.

Last date = MAXX(FILTER(all(Sheet1),Sheet1[Date]<max(Sheet1[Date])),Sheet1[Date])

Sales last date = 
var _dt =[Last date] return
CALCULATE(SUM(Sheet1[Sales]),FILTER(all('Date'),'Date'[Date]=_dt))

if you need more help make me @

Appreciate your Kudos.