Calculate a value from one table "with the condition" from another table

[inna_sysco]

Hi community!

I need your help in creating an appropriate DAX.

The task is - calculate the sum of Table[VALUE] for the current month, but for line, where "Chart section" = "Opening" calculate the sum of the previous month(but still display for the current month)

I assume that the algorithm should be like that:
1. Find the line in the "CONFIG" table, where "Chart section" = "Opening".
2. Select CODE for this line (CONFIG[Code])
3. Find the same code in "Table"[CODE]
4. Calculate sum of "Table[VALUE]" where Code = selected code from CONFIG[Code] and DAYTIME - previous month.

My .pbx file with model and some data is attached.
Could someone help me to solve this case?

Best wishes, Inna

[rajulshah]

Hello @inna_sysco ,

So what I understand is you want a single table with Current Month and Previous month numbers with the CHART_SECTION, right? Please let me know if my understanding is correct or not so that I can help you out.

[inna_sysco]

Hi, @rajulshah !
Thanks for the answer!
Yes, it's correct)
I need one matrix with different months in columns and values from Config[CHART_SECTION] in rows.
At the intersection of a column and a row should be displayed the value for the current month ( in the column for January 2021, the value for January 2021)
But for the row where Config[CHART_SECTION] = "Opening balance" should be displayed the previous month value (for ex: for January 2021, should be displayed value for December 2020)

[v-easonf-msft · Accepted solution]

Hi , @inna_sysco 

Try measures as below:

Pervious_value = 
VAR currentdate =
    SELECTEDVALUE ( 'TABLE'[DAYTIME] )
VAR previousdate =
    EDATE ( currentdate, -1 )
RETURN
    CALCULATE ( SUM ( 'TABLE'[VALUE] ), 'TABLE'[DAYTIME] = previousdate )

Result = 
IF (
    SELECTEDVALUE ( CONFIG[CHART_SECTION] ) = "Opening",
    [Pervious_value],
    SELECTEDVALUE ( 'TABLE'[VALUE] )
)

Please check my sample file for more  details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[inna_sysco]

@v-easonf-msft , if possible,  I have one more little question for you.
I tried to implement your solution for some custom ranges of dates... 
When I use the column with the original date (TABLE[Daytime]) it shows correct values just in line for the "previous month" (Opening) , all other values are wrong.. and I'm not able to configure the period (it display all months that have data)

When I use the custom column (Table[Format Daytime]) - I'm able to change the period, but values for the line "Opening" - BLANK...

How can I change measures to have all data on the same date axis?
My .pbx is attached.

[inna_sysco]

Hi @v-easonf-msft , thank you so much for the help, your solution works perfectly!