Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cobra77
Post Patron
Post Patron

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
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

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
Community Champion
Community Champion

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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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 others find it more quickly.

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

 

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.