Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi James,
Try this formula, please.
Measure = CALCULATE ( SUM ( Table1[UNITS] ), FILTER ( ALL ( 'Table1' ), Table1[TRUE/FALSE] = "TRUE" ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
Best Regards,
Hi James,
Try this formula, please.
Measure = CALCULATE ( SUM ( Table1[UNITS] ), FILTER ( ALL ( 'Table1' ), Table1[TRUE/FALSE] = "TRUE" ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
Best Regards,
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)
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.
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]))
Proud to be a Super User!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |