Reporting from the 15th of each month?

[Anonymous]

Hello All, 

I work for a non-profit and have a .pbix data model with related tables (snowflake schema) and a date table with all manner of boolean ( eg. iscurrentmonth) and other useful columns (eg. dayofmonth)

I would like to create a matrix visual - but instead of a date hierarchy of calendar months, I would need columns (or rows) such as:

Jan 15th - Feb14th

Feb15th - March 14th

March 15th - April 14th

I can't see how to achieve this and wondered about possible approaches (DAX / columns / power query?)

It's difficult to share the .pbix since I work with sensetive data and would take a lot of work to parse through only non PII.

Thanks in advance for any help.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

You need to create a range column. Please refer the following steps.

1. Create a column to group the date.

date Group = 
IF (
    DAY('Table'[Date]) >= 15
        && MONTH('Table'[Date]) = 12,
    1,
    IF ( DAY('Table'[Date]) >= 15 && MONTH('Table'[Date]) < 12, MONTH('Table'[Date]) + 1, MONTH('Table'[Date] )
))

2. Then we can create a column to meet your requirement.

Date range = 
var _min = CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[date Group]=EARLIER('Table'[date Group])))
var _max = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[date Group]=EARLIER('Table'[date Group])))
return
FORMAT(_min,"mmm") &" "& DAY(_min) &"-"& FORMAT(_max,"mmm") &" "& DAY(_max)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[ChrisMendoza]

@Anonymous - Seems similar to my recent response to https://community.powerbi.com/t5/Desktop/custom-period-for-calculate-sum/m-p/1485724#M617378 .

Check out the PBIX file attached in that post. See if that fits your need.

[Anonymous]

I'm just working with this solution and it looks promising. I really have to have another calendar table however?

[ChrisMendoza]

@Anonymous - You're looking at the attached file, correct? I think I left the 'Date' table there incase my idea didn't work out 😁.

You'll notice each table ('Date' & 'Custom Calendar') are both created by

CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Date = CALENDAR(DATE(2020,10,1),DATE(2020,12,31))

Pretty much accomplishes the same thing just two different ways dynamic vs. static.

I would add your custom definition need within your existing date table as additional columns. When you need to present your data by custom period use those columns. 👍