The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Team,
I need a graph or table or any visual for the following criteria,
We have Categories and their values with respective thier Month_years
We have slicer Month_year , if we select Oct 21 in our slicer then i need to see Sum of previous data(Aug-20, Aug-21, Sep-21) and oct 21 data separately by each category.
Finally what i need is either in table or bar.(if we select oct-21)
Category | Previous data | Selected year data |
Pen | 27 | 2 |
Fan | 54 | 14 |
Chair | 29 | 18 |
if we can shown in bar it should be great.
Thanks in advance,
Solved! Go to Solution.
Hi, @siva54
According to your description, I can clearly understand your requirement, I think you can try my unusual method to achieve your requirement simply:
First, you still need to get a date column for the comparison in the measure, you can create a calculated column like this and change the data type to get a date column simply:
Date = [Month_Year]
Then you can create a calculated table like this:
Slicer = SUMMARIZE('Table',[Month_Year],[Date])
Then create two measures:
Previous data = SUMX(FILTER('Table',[Date]<MAX('Slicer'[Date])),[Values])
Selected year data = SUMX(FILTER('Table',[Date]=MAX('Slicer'[Date])),[Values])
Then you can create a slicer and a table chart and place them like this:
And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @siva54
According to your description, I can clearly understand your requirement, I think you can try my unusual method to achieve your requirement simply:
First, you still need to get a date column for the comparison in the measure, you can create a calculated column like this and change the data type to get a date column simply:
Date = [Month_Year]
Then you can create a calculated table like this:
Slicer = SUMMARIZE('Table',[Month_Year],[Date])
Then create two measures:
Previous data = SUMX(FILTER('Table',[Date]<MAX('Slicer'[Date])),[Values])
Selected year data = SUMX(FILTER('Table',[Date]=MAX('Slicer'[Date])),[Values])
Then you can create a slicer and a table chart and place them like this:
And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @siva54
Understand your Month_year column is a Text Column, so use the following code to find the Previous data :
Previous data =
VAR _SelectedDate =
SELECTEDVALUE( 'Table'[Month_Year] )
VAR _A =
CALCULATETABLE(
ADDCOLUMNS(
'Table',
"Date", CONVERT( CONCATENATE( "01-", [Month_Year] ), DATETIME )
),
REMOVEFILTERS( 'Table'[Month_Year] )
)
VAR _C =
CONVERT( "1-" & _SelectedDate, DATETIME )
VAR _D =
FILTER( _A, [Date] < _C )
RETURN
IF( ISBLANK( SUMX( _D, [Values] ) ), 0, SUMX( _D, [Values] ) )
For Selected year data, add value column to the table set aggregation to Sum.
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @siva54
I think It's because of the blank items in the Month_Year column?
Can you share a PBIX file? [After removing Sensetive data] or dump your data in an excel file and share that with us to have better view on all data.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @VahidDM ,
Measure has been created , but when I'm dragging into a table then getting the below error.
I'm not able to upload the Excel file here.
Thanks in Advance.
@siva54 You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
User | Count |
---|---|
25 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |