Get currently monthly target sales amount and display in matrix

[Anonymous]

GoodDay,
I have a list of companies that have a list of target sales per month in a table.

If no selection is done on the date filter, the maximum monthly target sales amount in the dataset should be the default target sales displayed. (Eg Company1 would be 2021-04, 45881)

So when a selection is made from the date filter, it should display the corresponding monthly target sales based on the maximum month in the filter.

My DAX function currently selects the current monthly target sales based on the date filtered.

m_SelectedCurrentMonthlyTarget =

VAR SelectedSalesTarget = FORMAT(MAX(vw_Company_Sales[ReportPeriodTo].[Date]),"YYYY-MM")

Return

   CALCULATE([m_MonthlyTargetSales],

    FILTER(vw_ActiveMonthlySales, vw_ActiveMonthlySales[ReportingPeriod]= SelectedSalesTarget

       )

   )

When the date filter is filtered

When no filter is applied.  I need it to default to the month of 2021-04 and amount 45881

Then I want to display the latest monthly target sales together with a list of sales for the previous months for the same Company1

Your assistance and guidence is most highly appreciated. Thank you in advance.

[Anonymous]

Hi @Anonymous 

Thank you for your suggestions.

We eventually settled for having the two tables to display monthly sales and then the target sales for each month.

[Anonymous]

@Anonymous 
Thank you for your suggestions.
So we ended up with 2 tables, one for monthly sales and one for the monthly targets.

[Anonymous · Accepted solution]

@Anonymous 

You need create new measures:

2021-04 sales =  

IF(ISFILTERED([your slicer column]), [m_SelectedCurrentMonthlyTarget], 
CALCULATE([m_MonthlyTargetSales], FILTER(vw_ActiveMonthlySales, vw_ActiveMonthlySales[ReportingPeriod]="2021-04")))

For the last month and previous of the last month sales, I cannot say anything without seeing your mode. It would be more clear if you can share a sample pbix and the expected output.

For now, it can be something like:

last and last-1 month = CALCULATE(sum([sales]), Filter(allselected(table),[Company name]=Max([Company name]) && Month([Date]) >=Month(Maxx(All(table),[Date]))-1))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.