Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
Hi @Anonymous ,
If you have separate table for each month and they are the same data structure, it is suggested to append your tables. And then, use the measures above.
If you don't want to append them, try this:
substraction result =
VAR ThisMonthSales =
SWITCH (
SELECTEDVALUE ( Dates[Month] ),
7, SUM ( '2019/7/1'[Sales] ),
8, SUM ( '2019/8/1'[Sales] ),
9, SUM ( '2019/9/1'[Sales] ),
10, SUM ( '2019/10/1'[Sales] )
)
VAR LastMonthSales =
SWITCH (
SELECTEDVALUE ( Dates[Month] ),
7, 0,
8, SUM ( '2019/7/1'[Sales] ),
9, SUM ( '2019/8/1'[Sales] ),
10, SUM ( '2019/9/1'[Sales] )
)
RETURN
IF (
ISFILTERED ( Dates[MonthName] ),
IF ( ISBLANK ( LastMonthSales ), 0, ThisMonthSales - LastMonthSales ),
0
)
BTW, .pbix files attached.
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it more quickly.
Hi @Anonymous ,
I'm not very clear about your requirements. Please check if this is what you want:
substraction result =
VAR ThisMonthSales =
SUM ( 'Table'[Sales] )
VAR LastMonthSales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= EOMONTH ( MAX ( Dates[Date] ), -2 ) + 1
&& 'Table'[Date] <= EOMONTH ( MAX ( Dates[Date] ), -1 )
)
)
RETURN
IF (
ISFILTERED ( Dates[MonthName] ),
IF ( ISBLANK ( LastMonthSales ), 0, ThisMonthSales - LastMonthSales ),
0
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey
you solution seems pretty close to what im looking for. Not sure why im stuck here and not able to get the right result.
Just to mentioned i have separate table for each month and each table have data of single month only . i applied your measure as well but not sure why it is giving 0 result for each month.
please see below google doc where i captured some data -
Hi @Anonymous ,
If you have separate table for each month and they are the same data structure, it is suggested to append your tables. And then, use the measures above.
If you don't want to append them, try this:
substraction result =
VAR ThisMonthSales =
SWITCH (
SELECTEDVALUE ( Dates[Month] ),
7, SUM ( '2019/7/1'[Sales] ),
8, SUM ( '2019/8/1'[Sales] ),
9, SUM ( '2019/9/1'[Sales] ),
10, SUM ( '2019/10/1'[Sales] )
)
VAR LastMonthSales =
SWITCH (
SELECTEDVALUE ( Dates[Month] ),
7, 0,
8, SUM ( '2019/7/1'[Sales] ),
9, SUM ( '2019/8/1'[Sales] ),
10, SUM ( '2019/9/1'[Sales] )
)
RETURN
IF (
ISFILTERED ( Dates[MonthName] ),
IF ( ISBLANK ( LastMonthSales ), 0, ThisMonthSales - LastMonthSales ),
0
)
BTW, .pbix files attached.
Best regards
Icey
If this post helps, then consider Accepting it as the solution to help other members find it more quickly.
Hi @Anonymous
Can you provide some screenshots that explain your problem?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.