I have some data which looks like this: Financial_YearFinancial_MonthUNITSTRUE/FALSEFY17FM10540TRUEFY17FM11454TRUEFY17FM12543TRUEFY18FM01100TRUEFY18FM02150TRUEFY18FM03200TRUEFY18FM04230TRUEFY18FM05450TRUEFY18FM06560TRUEFY18FM0755TRUEFY18FM08475TRUEFY18FM09452TRUEFY18FM1045FALSEFY18FM11482FALSEFY18FM12458FALSE 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_TRUEFY17FM10540TRUE540FY17FM11454TRUE454FY17FM12543TRUE543FY18FM01100TRUE100FY18FM02150TRUE150FY18FM03200TRUE200FY18FM04230TRUE230FY18FM05450TRUE450FY18FM06560TRUE560FY18FM0755TRUE55FY18FM08475TRUE475FY18FM09452TRUE452FY18FM1045FALSE FY18FM11482FALSE FY18FM12458FALSE and i am now trying to use SAMEPERIODLASTYEAR to get:Financial_YearFinancial_MonthUNITSTRUE/FALSEUNITS_TRUEUNITS_LASTYEARFY17FM10540TRUE540 FY17FM11454TRUE454 FY17FM12543TRUE543 FY18FM01100TRUE100 FY18FM02150TRUE150 FY18FM03200TRUE200 FY18FM04230TRUE230 FY18FM05450TRUE450 FY18FM06560TRUE560 FY18FM0755TRUE55 FY18FM08475TRUE475 FY18FM09452TRUE452 FY18FM1045FALSE 540FY18FM11482FALSE 454FY18FM12458FALSE 543However,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

Helper I

7 years ago

Solved

SAMEPERIODLASTYEAR with filter

I have some data which looks like this:

Financial_Year	Financial_Month	UNITS	TRUE/FALSE
FY17	FM10	540	TRUE
FY17	FM11	454	TRUE
FY17	FM12	543	TRUE
FY18	FM01	100	TRUE
FY18	FM02	150	TRUE
FY18	FM03	200	TRUE
FY18	FM04	230	TRUE
FY18	FM05	450	TRUE
FY18	FM06	560	TRUE
FY18	FM07	55	TRUE
FY18	FM08	475	TRUE
FY18	FM09	452	TRUE
FY18	FM10	45	FALSE
FY18	FM11	482	FALSE
FY18	FM12	458	FALSE

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_Year	Financial_Month	UNITS	TRUE/FALSE	UNITS_TRUE
FY17	FM10	540	TRUE	540
FY17	FM11	454	TRUE	454
FY17	FM12	543	TRUE	543
FY18	FM01	100	TRUE	100
FY18	FM02	150	TRUE	150
FY18	FM03	200	TRUE	200
FY18	FM04	230	TRUE	230
FY18	FM05	450	TRUE	450
FY18	FM06	560	TRUE	560
FY18	FM07	55	TRUE	55
FY18	FM08	475	TRUE	475
FY18	FM09	452	TRUE	452
FY18	FM10	45	FALSE
FY18	FM11	482	FALSE
FY18	FM12	458	FALSE

and i am now trying to use SAMEPERIODLASTYEAR to get:

Financial_Year	Financial_Month	UNITS	TRUE/FALSE	UNITS_TRUE	UNITS_LASTYEAR
FY17	FM10	540	TRUE	540
FY17	FM11	454	TRUE	454
FY17	FM12	543	TRUE	543
FY18	FM01	100	TRUE	100
FY18	FM02	150	TRUE	150
FY18	FM03	200	TRUE	200
FY18	FM04	230	TRUE	230
FY18	FM05	450	TRUE	450
FY18	FM06	560	TRUE	560
FY18	FM07	55	TRUE	55
FY18	FM08	475	TRUE	475
FY18	FM09	452	TRUE	452
FY18	FM10	45	FALSE		540
FY18	FM11	482	FALSE		454
FY18	FM12	458	FALSE		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

Need Help

v-jiascu-msft

7 years ago

Hi James,

Try this formula, please.

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

Best Regards,

4 Replies

ryan_mayu
Super User
7 years ago
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)
- jhd
  Helper I
  7 years ago
  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.
  - ryan_mayu
    Super User
    7 years ago
    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]))

v-jiascu-msft

Microsoft Employee

7 years ago

Hi James,

Try this formula, please.

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

Best Regards,

Forum Discussion

SAMEPERIODLASTYEAR with filter

4 Replies

Related content

SAMEPERIODLASTYEAR()

Issue with SAMEPERIODLASTYEAR and Manual Time Intelligence

DAX SAMEPERIODLASTYEAR + Filter?

SAMEPERIODLASTYEAR with a year filter

SAMEPERIODLASTYEAR is blank