Measure calculation based on month slicer

[Anonymous]

Hi,

I have a dataset which contains the outstanding invoices for our customers at the end of each month + the YTD sales at the end of each month: so for January an overview of the outstanding invoices at 31.01.2020 and the YTD sales for january, for february the outstanding invoices at 29.02.2020 and the YTD sales for february.

I want to calculate the amount of days outstanding with the following calculation: (open items * month * 30) / YTD sales. 

The month should be based off my selection in a slicer; for January the calculation should be (open items * 1 * 30) / YTD sales, for April it should be (open items * 4 * 30) / YTD sales.

Is what i'm asking possible?

Below you can find a small sample of my dataset

Fiscal Year	Month	Name 1	Posting Date	Amount in local currency	Currency	Category
2020	Januari	Customer 1	31/01/2020	340.00	EUR	Open items
2020	Januari	Customer 1	31/01/2020	500.00	EUR	Open items
2020	Januari	Customer 2	31/01/2020	670.00	EUR	Open items
2020	Januari	Customer 3	31/01/2020	800.00	EUR	Open items
2020	Januari	Customer 5	31/01/2020	920.00	EUR	Open items
2020	Maart	Customer 4	30/03/2020	120.00	EUR	Open items
2020	Maart	Customer 2	30/03/2020	200.00	EUR	Open items
2020	Maart	Customer 3	30/03/2020	670.00	EUR	Open items
2020	Maart	Customer 5	30/03/2020	900.00	EUR	Open items
2020	Februari	Customer 4	31/01/2020	1,200.00	EUR	Open items
2020	Februari	Customer 3	19/07/2018	1,340.00	EUR	Open items
2020	Februari	Customer 2	1/01/2017	1,200.00	EUR	Open items
2020	Februari	Customer 4	1/01/2017	1,500.00	EUR	Open items
2020	Januari			100.00		Sales
2020	Januari			200.00		Sales
2020	Januari			300.00		Sales
2020	Januari			400.00		Sales
2020	Januari			100.00		Sales
2020	Januari			200.00		Sales
2020	Januari			300.00		Sales
2020	Februari			500.00		Sales
2020	Februari			300.00		Sales
2020	Februari			500.00		Sales
2020	Februari			700.00		Sales
2020	Februari			500.00		Sales
2020	Februari			300.00		Sales
2020	Februari			500.00		Sales
2020	Maart			800.00		Sales
2020	Maart			700.00		Sales
2020	Maart			600.00		Sales
2020	Maart			900.00		Sales
2020	Maart			800.00		Sales
2020	Maart			700.00		Sales
2020	Maart			600.00		Sales

[v-zhenbw-msft]

Hi @Anonymous ,

Do you want to calculate the Total open item in selected month / YTD sales.

For example, we select Jan, it will calculate the Total open item in Jan and the Total * 1 * 30, then the vaule / sales in Jan.

Based on your sample data, it will be (340 + 500 + 670 + 800 +920) * 1 *30 / (100 + 200 + 300 +400 + 100 + 200 + 300)

If yes, we can create two measures to meet your requirement.

YTD Sales = 
CALCULATE(SUM('Table'[ Amount in local currency ]),FILTER(ALL('Table'),'Table'[Category]="Sales"&&'Table'[Month]<=MAX('Table'[Month])))

Result = 
var _select = SELECTEDVALUE('Table'[Month name],"Jan")
var _select_month = CALCULATE(MAX('Table'[Month]),FILTER('Table','Table'[Month name]=_select))
var _Open = CALCULATE(SUM('Table'[ Amount in local currency ]),FILTER('Table','Table'[Category]="Open items")) * _select_month * 30
return
DIVIDE(_Open,[YTD Sales])

And put it in a table visual, the result like this.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Anonymous · Accepted solution]

Thanks for the suggestions, found the solution in the meanwhile.

Adding a calculation around the divide formula gave the desired result.

[amitchandak]

@Anonymous , Not clear try like

YTD Sales = CALCULATE(SUM(Table[Amount]),DATESYTD('Date'[Date],"12/31"),Table[Category] ="Sales")

or

YTD Sales = CALCULATE(SUM(Table[Amount]),DATESYTD('Date'[Date],"12/31"),filter(Table,Table[Category] ="Sales"))

open items = CALCULATE(Count(Table[Amount]),filter(Table,Table[Category] ="Open Items")) // replace count with sum if needed

new measure =divide([open items] * month(max(Table[Posting Date]) )* 30 ,[YTD Sales ])

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

or Averagex(Values(Table[Month],divide([open items] * month(max(Table[Posting Date]) )* 30 ,[YTD Sales ]))