Sales for Region are saved in two separate columns in my model

[Beerlover]

Sales for Region are saved in two separate columns in my model. For Region-A,B,C & F correct value is in Sales 1 and for Region - D, E - it is in Sales 2. By desired output is to have all regions together in one matrix table that contain column sales with correct sales value and further I have to roll up the data based on date selection to rolling 3,6,12 months and YTD.

Second part, I am using dynamic slicer in the table ...based on selection from the slicer we can also see Organisation->Account->Country->Region->province-> category-> sub-category->product -> Item( depends on end user what combination they choose).

@lbendlin @amitchandak @Ritaf1983 @Ashish_Mathur 

[Beerlover]

Thank you everyone for the response. I am checking it now and will respond soon.

[v-xuxinyi-msft · Accepted solution]

Hi @Beerlover 

@Ashish_Mathur , thanks for your concern about this case.

I added a Date column to your sample data. Hopefully the following will help.

1. Create a calculated column as follows

Final Sales = IF([Region] = "D" || [Region] = "E", [Sales2], [Sales1])

Result:

2. Create a calculated table to be used as a slicer

Date = CALENDAR(DATE(2024, 4, 1), TODAY())

Create the following measure to get rolling three-month data

R3M = 
VAR _last3months = EOMONTH(SELECTEDVALUE('Date'[Date]), -4)
VAR _diff = DATEDIFF(EOMONTH(SELECTEDVALUE('Date'[Date]), -1) + 1, SELECTEDVALUE('Date'[Date]), DAY) + 1
VAR _last3monthsresult = _last3months + _diff
RETURN
CALCULATE(SUM('Table'[Final Sales]), FILTER(ALLEXCEPT('Table', 'Table'[Region]), MAX([Date]) >= _last3monthsresult && SELECTEDVALUE('Date'[Date])))

Result:

You can calculate the rolling 6 month and rolling 12 month values in the same way.

3. Create a measure to calculate the YTD

YTD = 
VAR _date = SELECTEDVALUE('Date'[Date])
VAR _year = YEAR(_date)
RETURN
CALCULATE(SUM('Table'[Final Sales]), FILTER(ALLEXCEPT('Table', 'Table'[Region]), [Date] >= DATE(_year, 1, 1) && [Date] <= _date))

Result:

I'm a little confused about your second part request, could you please reflect the results you want in the sample data? That would be very helpful.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Sharing a picture is absolutely useless.  Atleast share data in a format that can be pasted in an MS Excel file.  Also, if you want running totals, then you shuould also have a date column.  Why have you not shown that?  Furthermore, when does the FY start?