Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Timeperiod slicer

Hi Folks .. I want to create a slicer where the data is sorted by Year,Month,Week,Day wise so there is a default visual in Power BI Iused but the issue is I just want past and present data to be fil...
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thanks for the reply ,

And I apologise for asking silly question , but I'm facing error  while creating measure 

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()) - 1 && 'YourData'[Date] < STARTOFMONTH(TODAY())),
"Present Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()))
)

For this mesaure I got few errors which I tried to resolve but still found new errors 
Error : A function 'STARTOFMONTH' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I tried one more :

CurrentDate=Today()

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(
FILTER(CalendarTable, 'CalendarTable'[Date] >= STARTOFMONTH(CalendarTable[CurrentDate]) - 1 && 'CalendarTable'[Date] < STARTOFMONTH(CalendarTable[CurrentDate]))
),
"Present Month", CALCULATETABLE(
FILTER(CalendarTable, 'CalendarTable'[Date] >= STARTOFMONTH(CalendarTable[CurrentDate]))
)
)

Error :The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

Even this also I tried :

StartOfMonth = STARTOFMONTH(CalendarTable[Date])

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(
FILTER(CalendarTable, [Date] >= [StartOfMonth] - 1 && [Date] < [StartOfMonth])
),
"Present Month", CALCULATETABLE(
FILTER(CalendarTable, [Date] >= [StartOfMonth])
)
)

Error :The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

Can u please suggest me more ...??

Thanks .

saud968's avatar
saud968
Icon for Memorable Member rankMemorable Member
2 years ago

No worries, I am also in a learning phase and not as expert as others but I will try to help you as much as possible. 

you have a table named 'CalendarTable' with a column 'Date' and a slicer table named 'SlicerTable' with a column 'DateRange,' you can create the following measures:

DAX

CurrentDate = TODAY()

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month",
CALCULATETABLE(
YourData,
FILTER(
'CalendarTable',
'CalendarTable'[Date] >= STARTOFMONTH(TODAY()) - 1 &&
'CalendarTable'[Date] < STARTOFMONTH(TODAY())
)
),
"Present Month",
CALCULATETABLE(
YourData,
FILTER(
'CalendarTable',
'CalendarTable'[Date] >= STARTOFMONTH(TODAY())
)
)
)
Make sure to replace 'YourData' with the actual name of your main data table. This measure filters your data based on the selected value in the 'SlicerTable' slicer, considering the "Last Month" and "Present Month" options.

Note: The 'YourData' table should have a relationship with the 'CalendarTable' on the date column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    yeah I tried this, its not working . 

    The error is in STARTOFMONTH() OR CALCULATETABLE() , I'm not getting. Why that STARTOFMONTH() is not accepting today() as parameter ,if we define it as varibale also it is raising same error as I mentioned above . I tried out all possible cases but still the error remains same .