Show Report Header as of Month and Year data is processed by

[vishy86]

Hi,

I have 2 columns Year (YYYY) and Month (MM). The report header needs to showcase the name of the month and year for which the data is processed  as of. The data processing for every month happens on the 15th of that month.

Example - If the date is 10th Sep 2019, then the data is only processed as of Aug 2019 and not Sep 2019, so the report header should show Aug 2019. Post 15th Sep 2019, the header should show Sep 2019 as the data is processed as of Sep.

How do I incorporate this logic using the 2 columns specified above.

Please show and help.

Thanks,

Vishy

[v-juanli-msft]

Hi @vishy86 

Create a calendar table and create relationship between calendar table and your table

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

Create calculated columns in calendar table

new month = var newm=IF(DAY([Date])>=15,[month],[month]-1) return IF(newm=0,12,newm)

new year = var newm=IF(DAY([Date])>=15,[month],[month]-1) return IF(newm=0,YEAR([Date])-1,[year])

new header = var newdate=DATE([new year],[new month],1) return FORMAT(newdate,"mmm yyyy")

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vishy86]

Hi Maggie,

Thanks for the detailed explanation. The requirement now has been modified and this was brought up yesterday.

The logic now is to keep checking the VALUATION_YR (YYYY) and VALUATION_MO (MM) and pick the latest year and month for which data is processes as of.

Example - If data is processed as of Sep 2019 then the VALUATION_YR and MO would have data for Sep 2019 which is the latest year month period and hence report header should show Sep 2019. 

I tried using MAX function for Month but it gives me September everytime as it is sorting the months alphabetically.

Can you please help me in tacking this.

Thanks,

Vishy

[vishy86]

Hi Maggie,

I found a solution to accomplish this. I concatenated the YYYY and MM column by creating a new column, changed the format to Date and then used FORMAT and MAX function to get the latest year and month.

If you have a better way of doing the same, please let me know.

Thanks again for your time and help.

Regards,

Vishy

[Greg_Deckler]

So typically you would create a measure that concatenates the latest year and month and then put that measure into a Card visual for display. Some people like to create a calculated column that is simply TODAY() so that this is updated when the report is refreshed. In your case, you will likely need another column (at least it would make it easier) which would be your month number (1-12). Then you could grab your MAX year and then the MAX month number and lookup your month name for that month number. 

Something along those lines.