cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Help with the below power BI question

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

1 ACCEPTED SOLUTION
Resolver III

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``````

3 REPLIES 3
Resolver III

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``````

Helper I

That worked.

Solution Supplier

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