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
jhd
Helper I
Helper I

SAMEPERIODLASTYEAR with filter

I have some data which looks like this:

 

Financial_YearFinancial_MonthUNITSTRUE/FALSE
FY17FM10540TRUE
FY17FM11454TRUE
FY17FM12543TRUE
FY18FM01100TRUE
FY18FM02150TRUE
FY18FM03200TRUE
FY18FM04230TRUE
FY18FM05450TRUE
FY18FM06560TRUE
FY18FM0755TRUE
FY18FM08475TRUE
FY18FM09452TRUE
FY18FM1045FALSE
FY18FM11482FALSE
FY18FM12458FALSE

 

and I am trying to create a forecast for "UNITS", but only using the values for which TRUE/FALSE = "TRUE"

 

A moving average would be nice, but I'd be happy with using just the number from the same month in previous year.

 

To that end, I have created the measure "UNITS_TRUE":

=CALCULATE(SUM(UNITS),FILTER(TRUE/FALSE)="TRUE")

which gives me:

Financial_YearFinancial_MonthUNITSTRUE/FALSEUNITS_TRUE
FY17FM10540TRUE540
FY17FM11454TRUE454
FY17FM12543TRUE543
FY18FM01100TRUE100
FY18FM02150TRUE150
FY18FM03200TRUE200
FY18FM04230TRUE230
FY18FM05450TRUE450
FY18FM06560TRUE560
FY18FM0755TRUE55
FY18FM08475TRUE475
FY18FM09452TRUE452
FY18FM1045FALSE 
FY18FM11482FALSE 
FY18FM12458FALSE 

 

and i am now trying to use SAMEPERIODLASTYEAR to get:

Financial_YearFinancial_MonthUNITSTRUE/FALSEUNITS_TRUEUNITS_LASTYEAR
FY17FM10540TRUE540 
FY17FM11454TRUE454 
FY17FM12543TRUE543 
FY18FM01100TRUE100 
FY18FM02150TRUE150 
FY18FM03200TRUE200 
FY18FM04230TRUE230 
FY18FM05450TRUE450 
FY18FM06560TRUE560 
FY18FM0755TRUE55 
FY18FM08475TRUE475 
FY18FM09452TRUE452 
FY18FM1045FALSE 540
FY18FM11482FALSE 454
FY18FM12458FALSE 543

However,

CALCULATE(SUM(UNITS),

FILTER(TRUE/FALSE)="TRUE",

SAMEPERIODLASTYEAR('CALENDAR'[DATE]))

does not work, I just get blanks.

 

Can anyone give me some pointers please?

Thanks, James

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi James,

 

Try this formula, please.

Measure =
CALCULATE (
    SUM ( Table1[UNITS] ),
    FILTER ( ALL ( 'Table1' ), Table1[TRUE/FALSE] = "TRUE" ),
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

SAMEPERIODLASTYEAR-with-filter

 

 

Best Regards,

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

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

Hi James,

 

Try this formula, please.

Measure =
CALCULATE (
    SUM ( Table1[UNITS] ),
    FILTER ( ALL ( 'Table1' ), Table1[TRUE/FALSE] = "TRUE" ),
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

SAMEPERIODLASTYEAR-with-filter

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ryan_mayu
Super User
Super User

@jhd

 

Could you please provide more detailed info? I tried this and it works on my end. You can refer to my coding.

 

test = 
var a= CALCULATE(SUM(Sheet31[amount]),SAMEPERIODLASTYEAR('Table'[Date]))
return if (SELECTEDVALUE(Sheet31[true/false])="True",a)

 

c1.JPGc2.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @ryan_mayu

 

I also need to get the values for 2018-10, 2018-11, and 2018-12 which are currently blank in your "test".

In other words, I still want to show a value in rows that have a "FALSE", but i want to show a blank when referencing it using SAMPEPERIODLASTYEAR.

 

 

@jhd

 

You can modify the coding and please note that you have to use the date, year or month value in the calendar table.

test = CALCULATE(SUM(Sheet31[amount]),SAMEPERIODLASTYEAR('Table'[Date]))

 

c1.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.