Filtering data on period on the same row

[KDew2020]

Hi,

I have a requirement to create a power bi report where I have following columns.

CustomerID

Sales DateTime

Sales amount

The report should look like below in which the sales and unique customer values should change based on the period selected

1. When day is selected last 1 day and last 2 days sales and customers should appear.
2. When week is selected last 1 week and last 2 weeks sales and customers should appear.
3. When month is selected last 1 month and last 2 months sales and customers should appear.
4. When YTD is selected only current YTD sales and customers should appear.

Is it doable?

[v-deddai1-msft · Accepted solution]

Hi @KDew2020 ,

What's the relationship between your date table and test table? You should create a one to many single relationship between them:

And I have combine your measure in one:

Calendar = ADDCOLUMNS(CALENDAR(TODAY()-366,TODAY()),"WEEK",WEEKNUM([Date]))

Selection measure =
VAR MySelection =
    SELECTEDVALUE ( Timeline[Period] )
VAR PreviousDaySales =
    CALCULATE (
        SUM ( Test[SalesAmount] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] >= TODAY () - 1 )
    ) + 0
VAR PreviousWeekSales =
    CALCULATE (
        SUM ( Test[SalesAmount] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[WEEK] >= WEEKNUM ( TODAY () ) - 1 )
    )
VAR PreviousMonthSales =
    CALCULATE ( SUM ( Test[SalesAmount] ), DATEADD ( 'Calendar'[Date], -1, MONTH ) )
RETURN
    SWITCH (
        TRUE (),
        MySelection = "Day", PreviousDaySales,
        MySelection = "Week", PreviousWeekSales,
        MySelection = "Month", PreviousMonthSales,
        PreviousDaySales
    )

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

Best Regards,

Dedmon Dai

[KDew2020]

Hi @v-deddai1-msft 

Unable to attach the pbix file here so explaning how I am trying to create  report.

1. I have a base table as below.

customerId	DateTime	SalesAmount	OrderAmount	CustomerType
101	2020-11-02	11	8	Employee
102	2020-11-02	12	9	Employee
103	2020-11-02	13	10	Contractor
104	2020-11-02	14	11	Employee
105	2020-11-06	15	12	Employee
106	2020-11-06	16	13	Contractor
107	2020-11-06	17	14	Employee
108	2020-11-06	18	15	Employee
109	2020-11-06	19	16	Contractor
110	2020-11-06	20	17	Employee
111	2020-11-06	21	18	Employee
112	2020-11-13	22	19	Contractor
113	2020-11-13	23	20	Employee
114	2020-11-13	24	21	Employee
115	2020-11-13	25	22	Contractor
116	2020-11-13	26	23	Employee
117	2020-11-13	27	24	Employee
118	2020-11-13	28	25	Contractor
119	2020-11-13	29	25	Employee
120	2020-11-13	30	25	Employee
121	2020-11-13	31	25	Contractor
122	2020-11-23	32	25	Contractor
123	2020-11-23	200	500	Employee
124	2020-11-23	31	975	Contractor
125	2020-11-23	32	1450	Employee
126	2020-11-23	200	1925	Contractor
127	2020-11-23	31	2400	Employee
128	2020-11-23	32	2875	Contractor
129	2020-11-23	200	3350	Employee
130	2020-11-23	31	3825	Contractor
131	2020-11-23	32	4300	Employee
132	2020-11-24	200	4775	Contractor
133	2020-11-24	31	5250	Employee
134	2020-11-24	32	5725	Contractor
135	2020-11-24	200	6200	Employee
136	2020-11-24	31	6675	Contractor
137	2020-11-24	32	7150	Employee
138	2020-11-24	200	7625	Contractor
139	2020-11-24	31	8100	Employee
140	2020-11-24	32	8575	Contractor
141	2020-11-24	200	9050	Employee
142	2020-11-24	31	9525	Contractor

1. Have created a disconnected table for period as below:

Period

Day

Week

Month

1. Create a date table as below:

Calendar = calendar (today()-366, today())

1. Created measures for last 1 day and 2 days as below

PreviousDaySales =

   var totalSales = CALCULATE (

        SUM ( Test[SalesAmount]),

        FILTER(ALL('Calendar'), 'Calendar'[Date] >= TODAY()-1)

    )

return if (totalSales = BLANK(),0,totalSales)

PreviousWeekSales =

   var totalSales = CALCULATE (

        SUM ( Test[SalesAmount]),

         FILTER(ALL('Calendar'), 'Calendar'[Date] >= TODAY()-6)

    )

return if (totalSales = BLANK(),0,totalSales)

PreviousMonthSales =

   var totalSales = CALCULATE (

        SUM ( Test[SalesAmount]),

         DATEADD ( 'Calendar'[Date],-1, MONTH )

    )

return if (totalSales = BLANK(),0,totalSales)

1. Then created a selection measure using switch and selectedvalue functions

Selection measure =

VAR MySelection =

SELECTEDVALUE ( Timeline[Period] )

RETURN

SWITCH (

TRUE (),

MySelection = "Day", [PreviousDaySales],

MySelection = "Week", [PreviousWeekSales],

MySelection = "Month", [PreviousMonthSales],

[PreviousDaySales]

)

it is working fine but when I add one more slicer “CustomerType” , it throws error for month (works fine for day and week).

Error Message:

MdxScript(Model) (95, 10) Calculation error in measure 'Test'[PreviousMonthsSales]: Function 'DATEADD' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship.

[v-deddai1-msft]

Hi @KDew2020 ,

Would you please inform us more detailed information( your sample pbix file  (by OneDrive for Business)) if possible? Then we will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[Fowmy]

@KDew2020 


You need to have a proper date table with a continuous date range, follow this article to learn more.

https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables

If you need a Date table, use the following code to create one by going to the Modeling tab. Click New Table and paste, adjust the date range as necessary.

Calendar = 
	ADDCOLUMNS(
		CALENDAR(date(2018,1,1),date(2021,12,31))
		,"Day Name",FORMAT([Date],"DDDD")
		,"Day of Week",(WEEKDAY([Date],1))
		,"Day of Month",DAY([Date]) 
		,"Week",DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))-(WEEKDAY([Date],1)-1)
		,"Week Name", "Week of " & DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))-(WEEKDAY([Date],1)-1)
		,"Week of Year", WEEKNUM([Date],1)
		,"Month", DATE(YEAR([Date]),MONTH([Date]),1)
		,"Month Name", FORMAT([Date],"MMMM")
		,"Month of Year", MONTH([Date])
		,"Month Year Name", FORMAT([Date],"MMM") & " " & YEAR([Date])
		,"Month Year Name Sort",(100*YEAR([Date])+MONTH([Date]))
		,"Quarter",DATE(YEAR([Date]),SWITCH(ROUNDUP(DIVIDE(MONTH([Date]),3,1),0),1,1,2,4,3,7,4,10),1)
		,"Quarter Name", "Q" & ROUNDUP(MONTH([Date])/3,0)
		,"Quarter Year Name", "Q" & ROUNDUP(MONTH([Date])/3,0) & " " & YEAR([Date])
		,"Year", DATE(YEAR([Date]),1,1)
		,"Year #",YEAR([Date])
	)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[KDew2020]

thank you @v-deddai1-msft and @Fowmy , Both solutions worked for me.

I have another column "customer type" (values: employee and contractor) and when I am trying to filter out the measures created as suggested using new slicer then I am getting beow error: 

"MdxScript(Model) (13, 20) Calculation error in measure 'Test'[PreviousDay]: Function 'DATEADD' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship"

how can I link them to filter the data with new slicer?

[v-deddai1-msft]

Hi @KDew2020 , 

Would you please try to create a disconnected table:

Then as mentioned by Fowmy, create a measure using SWITCH and SELECTEDVALUE function to show the measure based on the selection.

Use the period column as slicer and Column1,Column2 as column in matrix:

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

Best Regards,

Dedmon Dai

[Fowmy]

@KDew2020 

Try a disconnected Table method

You need to create a Data Table in your model and enter all period types you need like Day, Week and so on. This field can be used as a slicer on the report. Create a measure using SWITCH and SELECTEDVALUE function to show the measure based on the selection.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn