Dynamically group based on Date Periods

[ruthgammack]

Hi,

I have a report where i have periods and based on the current date vs the invoiced date end of month i want to set the period. For example if one is invoiced today it would look at the end of month as the 31st October 2021 and set the period to current.  if it was last month it would be period 1 etc. I think i need to use switch but im not 100% so any advice would be great. 

Thanks in advance.

[amitchandak · Accepted solution]

@ruthgammack , Not very clear

But a new column

Switch(true() ,

eomonth([Invoice Date], 0) = eomonth(today(), 0) , "Current" ,

datediff([Invoice Date], 0) , eomonth(today(), 0), month) & ""

)

[ruthgammack]

sorry will add more context. I need to be able to replicate this static report in Powerbi using the invoiced date and a link to the monthly periods table

In the graph right now it displays the period so '202206' 

but i want to dynamically change that as the months pass by so in this table i would expect the calculated Period to be