Forum Discussion

sbenzaquen's avatar
sbenzaquen
Helper I
5 years ago
Solved

Setting a reporting date

Hi,   I need help to find a solution to the following problem:   I'm building a report that summarizes how time is spent within the organization which is updated on a monthly basis. The trends fo...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  sbenzaquen ,

    I create some data:

    Here are the steps you can follow：

    Create calendar.

    Date = CALENDARAUTO()

    Report transaction time as of Mar-21:

    Create measure.

    report transaction time as of Mar-21 =
var _select= SELECTEDVALUE('Date'[Date])
return
IF(_select=DATE(YEAR(_select),3,21),
CALCULATE(MAX('Table'[transaction hour]),FILTER(ALL('Table'),'Table'[date]=_select)),
CALCULATE(MAX('Table'[transaction hour]),FILTER(ALL('Table'),'Table'[date]=DATE(YEAR(_select),3,21))))

    Growth vs. prior month:

    1. Create measure.

    This month =
var _date=SELECTEDVALUE('Date'[Date])
return
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),[date]>=DATE(YEAR(_date),MONTH(_date),1)&&[date]<=_date))
    Last month =
var _date=SELECTEDVALUE('Date'[Date])
return
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),[date]>=DATE(YEAR(_date),MONTH(_date)-1,1)&&[date]<=DATE(YEAR(_date),MONTH(_date)-1,DAY(_date))))

    2. Using [Date] in the Date table as a slicer, put [This month] and [Last month] into the KPI.

    Result:

     

    Best Regards,

    Liu Yang

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

amitchandak's avatar
amitchandak
Super User
5 years ago

sbenzaquen , You can create a new column like and use that to set this month or last month

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

 

 

you can use time intelligence for MOM

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

sbenzaquen's avatar
sbenzaquen
Helper I
5 years ago

Thank you, but this doesnt answer my question.

 

I know about DATEADD function

 

However I like to set a variable/paramenter so the dashboard provide information for a specific month.

 

For instance if the report is for Mar-21. I will see in the report transaction time as of Mar-21, growth vs. prior month....and other measures I calculated

 

If report is for Dec-21,I will see in the report transaction time as of Mar-21, growth vs. prior month....and other measures I calculated