Display date range as a column header

[Anonymous]

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

[Anonymous]

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

[v-kelly-msft · 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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[Anonymous]

Sorry

[Anonymous]

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] )

[amitchandak]

@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
))

[Pragati11]

Hi @Anonymous ,

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

[amitchandak]

@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

[Pragati11]

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