Monthy expense report with daily and fix charges

[B_kumar]

How can i  build a store wise monthly report for total charges from below table_1 and table_2

total charges: = Table_1[Charges]+Table_2[Monthly charges]

Table_1

Date	Stores	Charges
01-Jul-22	Store1	350
01-Jul-22	Store2	100
02-Jul-22	Store1	500
02-Aug-22	Store2	1200
03-Aug-22	Store1	200
03-Aug-22	Store2	50
04-Aug-22	Store1	50
04-Aug-22	Store2	50

Fixed monthly charges for each month in table_2

Table_2

Store	Monthly charges
Store1	2000
Store2	3000

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your expected visualization looks like, but please check the below picture and the attached pbix file.

Monthly charge measure: =
IF (
    SUM ( Table_1[Charges] ) = BLANK (),
    BLANK (),
    SUM ( Table_1[Charges] ) + SUM ( Table_2[Monthly charges] )
)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your expected visualization looks like, but please check the below picture and the attached pbix file.

Monthly charge measure: =
IF (
    SUM ( Table_1[Charges] ) = BLANK (),
    BLANK (),
    SUM ( Table_1[Charges] ) + SUM ( Table_2[Monthly charges] )
)

[B_kumar]

Thanks you for support, you did this so cleanly.

I am still learning the dax and just want to know how is it working ?

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Table_1 is filtered by the calendar table, and a SUM function that aggregates Table_1 is influenced by the calendar table. However, Table_2 is not filtered by the calendar table in this data model, and a SUM function that aggregates Table_2 is not influenced by the calendar table, and it shows same amount in every month.

I hope this helps you to have better understanding.

Thanks.