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.
hi community, I'm asking for your help ; I have in my dataset four columns Asset ID, Date, TotalAmount and SubtractionByMonth. I wish to make a total subtraction of TotalAmount by deducing the column SubtractionByMonth according to the 18 following months and to display each month with the remainder to deduct in columns even if there is nothing to deduct before turning off the 18 months, in this cases display Zeros until the 18th month. Thanks in advance.
Solved! Go to Solution.
Hi @Anonymous ,
We can create a calendar table first (you can use yours)
CalendarTable = ADDCOLUMNS(CALENDARAUTO(),"MonthYear",FORMAT([Date],"YYYYMM"))
Then modify the measure as following:
Reminder =
VAR currentDate =
MAX ( 'CalendarTable'[Date] )
VAR AID =
DISTINCT ( 'Table'[Asset ID] )
VAR t =
ALLSELECTED ( 'CalendarTable' )
RETURN
IF (
DATEDIFF (
CALCULATE (
MIN ( 'CalendarTable'[Date] ),
ALLSELECTED ( 'CalendarTable' ),
'Table'[Asset ID] IN DISTINCT ( 'Table'[Asset ID] )
),
currentDate,
MONTH
) > 17,
BLANK (),
IF (
COUNTROWS (
FILTER (
SUMMARIZE (
FILTER ( t, [Date] <= currentDate ),
'CalendarTable'[Date].[Year],
'CalendarTable'[Date].[MonthNo],
"Temp",
VAR td =
DATE ( [Date].[Year], [Date].[MonthNo] + 1, 1 ) - 1
RETURN
CALCULATE (
CALCULATE (
SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
t,
'Table'[Date] <= td,
'Table'[Asset ID] IN AID
)
)
),
[Temp] <= 0
)
) > 0,
0,
CALCULATE (
SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
ALLSELECTED ( 'Table' ),
'Table'[Date] <= currentDate,
'Table'[Asset ID] IN AID
)
)
)
By the way, PBIX file as attached.
Best regards,
Hi @Anonymous ,
We can try to use the following measure to meet your requirement:
Reminder =
VAR currentDate =
MAX ( 'Table'[Date] )
VAR AID =
DISTINCT ( 'Table'[Asset ID] )
VAR t =
ALLSELECTED ( 'Table' )
RETURN
IF (
DATEDIFF (
CALCULATE (
MIN ( 'Table'[Date] ),
ALLSELECTED ( 'Table' ),
'Table'[Asset ID] IN DISTINCT ( 'Table'[Asset ID] )
),
currentDate,
MONTH
) > 18,
BLANK (),
IF (
COUNTROWS (
FILTER (
SUMMARIZE (
FILTER ( t, [Date] <= currentDate ),
'Table'[Date].[Year],
'Table'[Date].[MonthNo],
"Temp",
VAR td =
DATE ( [Date].[Year], [Date].[MonthNo] + 1, 1 ) - 1
RETURN
CALCULATE (
CALCULATE (
SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
t,
'Table'[Date] <= td,
'Table'[Asset ID] IN AID
)
)
),
[Temp] <= 0
)
) > 0,
0,
CALCULATE (
SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
ALLSELECTED ( 'Table' ),
'Table'[Date] <= currentDate,
'Table'[Asset ID] IN AID
)
)
)
If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared?
By the way, PBIX file as attached.
Best regards,
Hi @Anonymous ,
The table looks as similar as we have shared before, how about the result after you follow the suggestions mentioned in my original post? Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hello , your proposal is good but the need that it's was asked for me is to display the months in column format no rows, but thank you for your effort in any case. calculate it is good.
But i still have some issues to how adapt with my own data.
Hi @Anonymous ,
How about the result after you follow the suggestions mentioned in my previous post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @Anonymous ,
We can create a calendar table first (you can use yours)
CalendarTable = ADDCOLUMNS(CALENDARAUTO(),"MonthYear",FORMAT([Date],"YYYYMM"))
Then modify the measure as following:
Reminder =
VAR currentDate =
MAX ( 'CalendarTable'[Date] )
VAR AID =
DISTINCT ( 'Table'[Asset ID] )
VAR t =
ALLSELECTED ( 'CalendarTable' )
RETURN
IF (
DATEDIFF (
CALCULATE (
MIN ( 'CalendarTable'[Date] ),
ALLSELECTED ( 'CalendarTable' ),
'Table'[Asset ID] IN DISTINCT ( 'Table'[Asset ID] )
),
currentDate,
MONTH
) > 17,
BLANK (),
IF (
COUNTROWS (
FILTER (
SUMMARIZE (
FILTER ( t, [Date] <= currentDate ),
'CalendarTable'[Date].[Year],
'CalendarTable'[Date].[MonthNo],
"Temp",
VAR td =
DATE ( [Date].[Year], [Date].[MonthNo] + 1, 1 ) - 1
RETURN
CALCULATE (
CALCULATE (
SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
t,
'Table'[Date] <= td,
'Table'[Asset ID] IN AID
)
)
),
[Temp] <= 0
)
) > 0,
0,
CALCULATE (
SUM ( 'Table'[TotalAmount] ) - SUM ( 'Table'[SubtractionByMonth] ),
ALLSELECTED ( 'Table' ),
'Table'[Date] <= currentDate,
'Table'[Asset ID] IN AID
)
)
)
By the way, PBIX file as attached.
Best regards,
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.
User | Count |
---|---|
16 | |
2 | |
2 | |
1 | |
1 |