Month over month graph

[KiravonEhren]

Hello all,

I created a graph which shows the ROAS for this week and the previous week (x Axis = Day of week and y Axis= ROAS):

The measure for the ROAS of the previous week is calculated as follows : 

ROAS previous week =
Divide(

calculate(
sum('All data'[NetRevenue (Attributed, ToCnv)]),
'Date'[Week Number]=SELECTEDVALUE('Date'[Week Number])-1,
'Date'[Weekday number]=SELECTEDVALUE('Date'[Weekday number]),
'All data'[Costs (Total, Commission, ToCnv)]<>0),

calculate(
SUM('All data'[Costs (Total, Commission, ToCnv)]),
'Date'[Week Number]=SELECTEDVALUE('Date'[Week Number])-1,
'Date'[Weekday number]=SELECTEDVALUE('Date'[Weekday number]),
'All data'[Costs (Total, Commission, ToCnv)]<>0)
)

Now I wanted to do the same graph but instead of a week over week comparison with a month over month comparison: 

I tried to use the following formula to get the ROAS of the  previous month on a daily bases:

ROAS per Day previous Month =

divide(

calculate(sum('All data'[NetRevenue (Attributed, ToCnv)]),

'Date'[Month Number]=SELECTEDVALUE('Date'[Month Number])-1,

'Date'[Day Number]=SELECTEDVALUE('Date'[Day Number]),

'All data'[Costs (Total, Commission, ToCnv)]<>0),

calculate(SUM('All data'[Costs (Total, Commission, ToCnv)]),

'Date'[Month Number]=SELECTEDVALUE('Date'[Month Number])-1,

'Date'[Day Number]=SELECTEDVALUE('Date'[Day Number]),

'All data'[Costs (Total, Commission, ToCnv)]<>0)

)

However, the formula for month does not work even though I just copied the formula from the week over week comparison and exchanged week number for month number and weekday for day number...

Does anyone have an idea why?

Thank you very much! 🙂 

[v-yiruan-msft · Accepted solution]

Hi  @KiravonEhren,Please try to update the formula of measure [ROAS per Day previous Month] as below and check if can return the correct result...

PS: The part with red font is update ones....

ROAS per Day previous Month =
DIVIDE (
    CALCULATE (
        SUM ( 'All data'[NetRevenue (Attributed, ToCnv)] ),
        PREVIOUSMONTH ( 'All data'[Date] ),
        'All data'[Costs (Total, Commission, ToCnv)] <> 0,
        ALL ( 'All data' )
    ),
    CALCULATE (
        SUM ( 'All data'[Costs (Total, Commission, ToCnv)] ),
        PREVIOUSMONTH ( 'All data'[Date] ),
        'All data'[Costs (Total, Commission, ToCnv)] <> 0,
        ALL ( 'All data' )
    )
)

How to create Dax logic for this Month and Previous Month

Values of previous month

---

= CALCULATE(SUM(Table[Auftragsbestand]); VALUES(Table[PG]);DATEADD(Table[Datum];-1;MONTH))

---

Message error

If the above one can't help you get the desired result, please provide some sample data in your tables'All data'  and'Date'(exclude sensitive data) with Text format and yourexpected result with backend logic, screenshot of your visuals and special examples. By the way, is there any relationship created between the table 'All data'  and 'Date'? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[KiravonEhren]

Hello Ying, 

Thank you for your reply. This solution worked! 🙂 
Could you explain to me where my thinking was wrong and why my formula did not work? 
 Thank you so much, your help is highly appreciated!

Kira