Help with the below power BI question

[grkanth81]

Hi all,

I would like to create a slicer with values 30 Days, 60 Days, 90 Days, 120 Days and Financial Year to date.

Financial year to date is 01/07/2023 in this scenario when you run the report today.

Here is the dummy data:

company	Status	Date won or loss
ABC	won	29/03/2024
XYZ
MDR	won	30/03/2024
ABC	won	2/04/2024
KFC	Loss	6/04/2024
UFO	won	1/03/2024
ABC	won	9/02/2024
BP	won	5/01/2024
MDR	won	12/12/2023
UFO
MDR	won	10/08/2023
DKL	won	9/09/2023
MDR	won	2/05/2023
DCT	won	18/04/2023
UFO	won	20/03/2023

I would like to create a table which shows summary of the above data groued by Company and 

when the user selects 30 Days the table should show the quantity won in the last 30 days from the report run date

when the user selects 60 Days the table should show the quantity won in the last 60 days from the report run date

when the user selects 90 Days the table should show the quantity won in the last 90 days from the report run date

when the user selects 120 Days the table should show the quantity won in the last 120 days from the report run date

when the user selects Financial Year to date the table should show the quantity won in the Financial Year to date (which is since 01/07/2023) 

For example when the user selects 30 Days - ABC should show won as 2, when the user selects 60 Days - ABC should show won as 2, when the user selects 90 Days - ABC should show won as 3, when the user selects 120Days - ABC should show won as 3, when the user selects Financial Year to date - ABC should show won as 3.

Can you please provide your assistance and let me know how to achieve above ?

Thanks

[aduguid · Accepted solution]

You could create this DAX table and link it your date. Then set the sort column of "Ageing Days" to "Ageing Days Order". After that create a slicer from the Ageing Days" column.

  

Calendar Ageing = 
VAR _today_date =        TODAY()
VAR _future_date =       CALCULATE(MIN(YourTable[Your_Date]))
VAR _fiscal_year =       YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start = DATE ( _fiscal_year - 3, 07, 01)
VAR _fiscal_year_end =   DATE ( _fiscal_year, 06, 30)
 
VAR _result = 
UNION (
      ADDCOLUMNS (CALENDAR ( _today_date - 30, _today_date),		"Ageing Days", "30 Days",		"Ageing Days Order", 1)
    , ADDCOLUMNS (CALENDAR ( _today_date - 60, _today_date),    	"Ageing Days", "60 Days",		"Ageing Days Order", 2)
    , ADDCOLUMNS (CALENDAR ( _today_date - 90, _today_date),		"Ageing Days", "90 Days",		"Ageing Days Order", 3)
    , ADDCOLUMNS (CALENDAR ( _today_date - 120, _today_date),   	"Ageing Days", "120 Days",		"Ageing Days Order", 4)
	, ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _fiscal_year_end),	"Ageing Days", "YTD",			"Ageing Days Order", 6)
)

RETURN
_result

[grkanth81]

Thank you aduguid

That worked.

[Joe_Barry]

Hi @grkanth81 

It's a bit of a read, but this article shoudl hopefully answer your questions about calculating the fiscal amounts using a fiscal calendar

https://www.daxpatterns.com/custom-time-related-calculations/

Here is the measure that calculates the last months. Just add your new measure that you create above to this

• Create a table with the column Cal and a column ID and then in the table view sort Cal by the ID
  

  CAL	ID
  L30D	1
  L60D	2
  L90D	3
  L120D	4

• Create a measure in this table

CALC = MIN(NewTable[ID])​

• Then Create this measureand Repeat for each time Frame

Total - Last 120 Days = 
CALCULATE (
    [YourNewFiscalMeasure],
    DATESINPERIOD ( 'DIM_Date'[Date], MAX ( 'DIM_Date'[Date] ), -120, DAY)
)​

• The create this measure

SelCalc = SWITCH([Calc],
1, [Total - Last 30 Days],
2, [Total - Last 60 Days],
3, [Total - Last 90 Days],
4, [Total - Last 120 Days])​

• Add a slicer to the canvas and add the Cal column from the generated table and make it single select
• Add the SelCalc measure to a visual and add a Date, Year or Month also
• You can then switch between calculations by selecting the filter

Joe

If you found my answer helpful and it solved your issue, please accept as solution