cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cobra77 Member
Member

Show previous periode data if no data on period selected

hi,

 

example of my data : 

 

sample_nodata.jpg

 

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

 

 

 

 

 

 

 

6 REPLIES 6
Super User
Super User

Re: Show previous periode data if no data on period selected

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))

 

Community Support Team
Community Support Team

Re: Show previous periode data if no data on period selected

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)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cobra77 Member
Member

Re: Show previous periode data if no data on period selected

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

Community Support Team
Community Support Team

Re: Show previous periode data if no data on period selected

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cobra77 Member
Member

Re: Show previous periode data if no data on period selected

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

 

explain_mes.jpgexplain_mes2.jpg

 

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

 

Highlighted
Cobra77 Member
Member

Re: Show previous periode data if no data on period selected

@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

 

explain_mes3.jpg

 

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

 

thanks for your helps