Show previous periode data if no data on period selected

[Cobra77]

hi,

example of my data : 

the date is connected with calendar table.

we want show the first 2 line of January if user selected 2018-01-01 or 2018-02-01 or 2018-03-01

and show the 3rd line if user selected 2018-04-01

for the values and text like description ( the whole line )

It is possible ?

thanks for your helps

Best regards

thanks

[v-frfei-msft]

Hi @Cobra77,

I made one sample for your reference. Please check the steps as below.

1. Create a data table. Here we should not create relationshiop between the data table and the fact table.

Table = CALENDARAUTO()

2.Create a measure as below.

Measure = var seledate = SELECTEDVALUE('Table'[Date])
return
IF(MAX(Table1[Date])<=seledate,1,0)

For more details, please check the pbix as attached.

Regards,

Frank

[Cobra77]

Hi @v-frfei-msft

Thanks for your answer.

But i don t want show the lines of January if i choose 2018-04-01 , juste the april line ( in that case)

I must test if for the period i have line, else show only the last period with data.

Thanks

[Stachu]

so only sales within same quarter? one of these should work depending whether when selecting Feb you only want to see
Jan-Feb 

Measure1 = CALCULATE(SUM(Table[Column]),DATESQTD(Calendar[Date]))

 or Jan-Mar

Measure2 = CALCULATE(SUM(Table[Column]),PARALLELPERIOD(Calendar[Date],0,QUARTER))

[v-frfei-msft]

Hi @Cobra77,

Try this new measure.

Measure 3 = var seledate = SELECTEDVALUE('Table'[Date])
var dateo = MAX(Table1[Date])
var countr = CALCULATE(COUNTROWS(Table1),FILTER(ALL('Table1'),'Table1'[Date] = SELECTEDVALUE('Table'[Date])))
return
IF(countr = 1 && seledate = dateo,1,IF(countr<>1 && seledate>=dateo,1,0))

Regards,

Frank

[Cobra77]

Hi @v-frfei-msft,

I don t understand why it s not ok with ssas tabular/pbix.

I filter on page on february , and Affaire id = 2

Special Mesure:=var seledate = IF (HASONEVALUE( 'TableFilter'[PER_ID]); VALUES('TableFilter'[PER_DATE_DEBUT]))
var idperiode = IF (HASONEVALUE( 'TableFilter'[PER_ID]); VALUES('TableFilter'[Id]))
var idaff = IF (HASONEVALUE( '01 - Affaires'[AFF_ID]);VALUES('01 - Affaires'[Id]))

var datePrevMax = CALCULATE(MAX('Data'[PER_DATE_DEBUT]);FILTER(ALL('Data');'Data'[PER_DATE_DEBUT]< seledate && 'Data'[FK_IdAffaire] =idaff))
var idPerPrevMax = CALCULATE(AVERAGE( 'Data'[FK_IdPeriode]);FILTER('Data';'Data'[PER_DATE_DEBUT]=datePrevMax && 'Data'[FK_IdAffaire] = idaff))

var countr = CALCULATE(COUNTROWS('Data');FILTER('Data';'Data'[FK_IdPeriode] = idperiode && 'Data'[FK_IdAffaire]= idaff ))
var countr2 = CALCULATE(COUNTROWS('Data');FILTER('Data';'Data'[FK_IdPeriode] = idPerPrevMax && 'Data'[FK_IdAffaire]=idaff))

return
IF(countr = 0 ; countr2 ; countr )

show all detail calculs with the cards visualisation, its ok.

The Measure show well 2 for february , but i ve 1 for all lines ?

An idea ?  thanks

[Cobra77]

@v-frfei-msft

If i change just the return its ok for all this measures :

Mesure Special 2 : ... ( the same that special )

return  countr     ( juste change return )

Mesure Special 3 : ... ( the same that special )

return  countr2     ( juste change return )

Special Mesure 4 ... ( the same that special )

return IF(  countr > 0 ; countr )  its ok too

Why with return
IF(countr  > 0 ; countr ; countr2 )    its KO ????

thanks for your helps