Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Display date range as a column header

Hello Community

I have a report that uses the table visual with columns that show a count of “X” between various date ranges.  I have used a DimDate table linked to a Fact table with 4 joins using the “USERELATIONSHIP” function.

 

They are all working well but I wondered if there was anyway to display the different date ranges above each column, to give the user confidence that they are looking at the correct data as it changes each month dependent on a date slicer.

 

Ideally this would be in the column headers but if not I could put the date range in cards above each column.

 

Many thanks

1 ACCEPTED SOLUTION

Hi  @Anonymous ,

 

You could create 3 measures as below:

Fils Received Prev Month = 
var _mindate=CALCULATE(MIN('Slicer table'[Date]),PREVIOUSMONTH('Slicer table'[Date]))
var _maxdate=CALCULATE(MAX('Slicer table'[Date]),PREVIOUSMONTH('Slicer table'[Date]))
Return
_mindate& " - "&_maxdate
Fils Received Prev Month last year = 
var _table=CALCULATETABLE(VALUES('Slicer table'[Date]),PREVIOUSMONTH('Slicer table'[Date]))
var _mindate=CALCULATE(Min('Slicer table'[Date]),DATEADD(_table,-1,MONTH))
var _maxdate=CALCULATE(MAX('Slicer table'[Date]),DATEADD(_table,-1,MONTH))
Return
_mindate& " - "&_maxdate
_Month to date = SELECTEDVALUE('Slicer table'[Date]) & " - Today"

And you will see:

v-kelly-msft_0-1609919127005.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Sorry I wasn't clear, the DAX is fine but I hope the image below will explain what I am trying to achieve

Charlie_Durnin_0-1609763800577.png

 

Hi  @Anonymous ,

 

You could create 3 measures as below:

Fils Received Prev Month = 
var _mindate=CALCULATE(MIN('Slicer table'[Date]),PREVIOUSMONTH('Slicer table'[Date]))
var _maxdate=CALCULATE(MAX('Slicer table'[Date]),PREVIOUSMONTH('Slicer table'[Date]))
Return
_mindate& " - "&_maxdate
Fils Received Prev Month last year = 
var _table=CALCULATETABLE(VALUES('Slicer table'[Date]),PREVIOUSMONTH('Slicer table'[Date]))
var _mindate=CALCULATE(Min('Slicer table'[Date]),DATEADD(_table,-1,MONTH))
var _maxdate=CALCULATE(MAX('Slicer table'[Date]),DATEADD(_table,-1,MONTH))
Return
_mindate& " - "&_maxdate
_Month to date = SELECTEDVALUE('Slicer table'[Date]) & " - Today"

And you will see:

v-kelly-msft_0-1609919127005.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

Anonymous
Not applicable

Sorry

Charlie_Durnin_0-1609762132005.png

 

Anonymous
Not applicable

I have added a screenshot below

The DAX for the third column is

Files Recieved Prev Month =
CALCULATE (
'Performance Against Target'[Count of 1629],
PREVIOUSMONTH ( DimDate[Date] ),
USERELATIONSHIP ( 'Performance Against Target'[1629], DimDate[Date] )
 
The DAX for the 4th column is
 
Files Recieved Previous Month Last Year =
CALCULATE (
'Performance Against Target'[Count of 1629],
DATEADD (
DimDate[Date],
-13,
MONTH
),
USERELATIONSHIP ( 'Performance Against Target'[1629], DimDate[Date] )
 

@Anonymous , Try measures like, [Count of 1629] should be a measure or use

count('Performance Against Target'[Count of 1629]) in place of 'Performance Against Target'[Count of 1629]

 

CALCULATE (
CALCULATE (
'Performance Against Target'[Count of 1629],
USERELATIONSHIP ( 'Performance Against Target'[1629], DimDate[Date] )), 
PREVIOUSMONTH ( DimDate[Date] )
)

 
The DAX for the 4th column is
 
Files Recieved Previous Month Last Year =
CALCULATE (
CALCULATE (
'Performance Against Target'[Count of 1629],USERELATIONSHIP ( 'Performance Against Target'[1629], DimDate[Date] )),
DATEADD (
DimDate[Date],
-13,
MONTH
))

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @Anonymous ,

 

Can not see any screesnhot in your response. Can you add again?

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

amitchandak
Super User
Super User

@Anonymous , not very clear. See if this blog can help

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Pragati11
Super User
Super User

Hi @Anonymous ,

 

Can you add some additional information like sample data, screenshots of your report and what is required?

Your query is incomplete without any screenshots.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.