Get value from recent quarter based on date and month column.

[NickzNickz]

Hi ... 

I have below table (sample data).

Date	Year	Month	Purchase	Loan	Rental
31/3/2020	2020	3	6,154.57	1,005.50	867.71
30/6/2020	2020	6	1,144.23	520.21	630.16
30/9/2020	2020	9	211.06	34.68	328.65
31/12/2020	2020	12	520.21	540.28	848.30
31/12/2020	2020	13	1,144.23	520.21	630.16
31/3/2021	2021	3	937.13	551.11	322.45
30/6/2021	2021	6	1,093.46	211.06	78.53
30/9/2021	2021	9	621.56	118.47	231.10
31/12/2021	2021	12	46.20	937.13	1,337.47
31/12/2021	2021	13	937.13	551.11	322.45
31/3/2022	2022	3	497.50	222.12	46.20
30/6/2022	2022	6	11,947.51	4.17	322.45
30/9/2022	2022	9	1,434.00	893.86	621.56
31/12/2022	2022	12	879.00	4,620.00	497.50
31/3/2023	2023	3	937.13	540.28	322.45
30/6/2023	2023	6	211.06	937.13	848.30

For month december, I will have two record. Month 12 is a first record and Month 13 is a final record. If month 13 not exist, dax should pickup data from month 12.

From above statement, how can I creata a view/working table from above master data  as sample below.

View/Working  Table #1:

Date	Description	Amount
31/3/2020	Purchase	6,154.57
31/3/2020	Loan	1,005.50
31/3/2020	Rental	867.71
30/6/2020	Purchase	1,144.23
30/6/2020	Loan	520.21
30/6/2020	Rental	630.16
30/9/2020	Purchase	211.06
30/9/2020	Loan	34.68
30/9/2020	Rental	328.65
31/12/2020	Purchase	1,144.23
31/12/2020	Loan	520.21
31/12/2020	Rental	630.16

View/Working Table #2:

Date	Purchase	Loan	Rental
31/3/2020	6,154.57	1,005.50	867.71
30/6/2020	1,144.23	520.21	630.16
30/9/2020	211.06	34.68	328.65
31/12/2020	1,144.23	520.21	630.16

After that, I want to create a measure to display data in my report. Example, if current date is 31/08/2020, the result should display quarter 2 value based on category.

I hope my explanation is clear... 🙂 

Regards,

NickzNickz

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[ThxAlot]

13mth.pbix

Aren't those two vizs the same? Anyway, easy enough

[NickzNickz]

Hi @ThxAlot ,

The scenario is like this, every quarter we will recieved quarter record with cumulative amount. Record for 31/12/2020 will have month 13 after the amount has been finalized. Usualy the amount/record will only available somewhere mids of next year. i.e 31/07/2021. It will supersede the original amount.

When we slicer by date, the record should display amount from month 13 if available. If not, use amount from month 12. My chart also will relfect. For info, my chart will have recent 3 years record (completion of the quarter cyle for that year)

Please find table below as reference.

Currently I only have the TableMaster format (please refer to my original table format). I wish to have/create the above table as a view/working table instead of measure.  I will use the view/working table to create/design my reports. 

I hope this can clear my request... 😁

Your sample pbix file was great...  I really appreciate your effort. 

Regards,

NickzNickz

[Mahesh0016]

@NickzNickz  I hope this helps you. Thank You

> I'll guide you through the steps to achieve the two working tables and the measure you mentioned.

**Step 1: Create Working Table #1 (View/Working Table #1):**

This table displays the data in a row format for each combination of Date, Description, and Amount. You want to display the data for each month's first record (Month 12) and second record (Month 13).

ViewTable1 =
VAR FirstRecordOfMonth12 = FILTER('YourTable', 'YourTable'[Month] = 12)
VAR FirstRecordOfMonth13 = FILTER('YourTable', 'YourTable'[Month] = 13)
VAR CombinedRecords = UNION(FirstRecordOfMonth12, FirstRecordOfMonth13)
RETURN
SELECTCOLUMNS(
CombinedRecords,
"Date", 'YourTable'[Date],
"Description", "Purchase", "Amount", 'YourTable'[Purchase]
)
UNION
SELECTCOLUMNS(
CombinedRecords,
"Date", 'YourTable'[Date],
"Description", "Loan", "Amount", 'YourTable'[Loan]
)
UNION
SELECTCOLUMNS(
CombinedRecords,
"Date", 'YourTable'[Date],
"Description", "Rental", "Amount", 'YourTable'[Rental]
)

**Step 2: Create Working Table #2 (View/Working Table #2):**

This table displays the data in a column format for each category (Purchase, Loan, Rental) and Date.

ViewTable2 =
VAR FirstRecordOfMonth12 = FILTER('YourTable', 'YourTable'[Month] = 12)
VAR FirstRecordOfMonth13 = FILTER('YourTable', 'YourTable'[Month] = 13)
VAR CombinedRecords = UNION(FirstRecordOfMonth12, FirstRecordOfMonth13)
RETURN
SUMMARIZE(
CombinedRecords,
'YourTable'[Date],
"Purchase", SUM('YourTable'[Purchase]),
"Loan", SUM('YourTable'[Loan]),
"Rental", SUM('YourTable'[Rental])
)

**Step 3: Create the Measure for Quarterly Data:**

To create a measure that displays data for a specific quarter based on the current date, you can use the following DAX code:

QuarterlyValue =
VAR CurrentQuarter = INT((MONTH(NOW()) - 1) / 3) + 1
RETURN
SWITCH(
CurrentQuarter,
1, SUM('YourTable'[Purchase]), // Q1
2, SUM('YourTable'[Loan]), // Q2
3, SUM('YourTable'[Rental]), // Q3
4, BLANK() // Q4 (if needed)
)

Replace `'YourTable'` with the actual name of your table in your data model.

[NickzNickz]

Hi @Mahesh0016 ,

I have successfully created the View/Working Table 2 as mentioned above after viewing some of videos on the YouTube... 😅 ... But the result should not combine both. Only select the latest month/value for Quarter 4. If month 13 exists, select month 13 else month 12. Quarters 1 - 3 only have 1 amount/value.

However, I am still stuck with some errors in the formula for View/Working Table 1 as below:

Hope @Mahesh0016  or anyone can assist me on this matter...

Thank you.

Regards,

NickzNickz