Forum Discussion

chlimaye's avatar
chlimaye
Regular Visitor
1 year ago

Currenmonth and Previous Month values along with comments

Hi ,

 

I have created one table which Market, Brand, Sub-Month , Volume , Curre- PreviousMonth Diff, Comments.

 

In data model i am havign two table one is DVMI and other DVMIBC with comments.

Two table has relatioship with composite key Market-Brand-SubMonth.

 

From DVMIBC we are fetchimg comments and rest of the fields i am getting fro DVMI table.

 

I am showing current month and previous month volume with the help of master calander for submonth.

We have comments for Some Market-brand-submonth.

 

For example,

Canada-ABC-202410 has comments in DVMIBC table and i am showing the comments filed in table and it got split up as we are showing previous month volumes along with curenmonth volume in same row with dax for previous month.

CALCULATE([Volume],
DATEADD(submissionDate[Date],-1,MONTH))
 
The requirement is we want data in same row with out any split. 
 without previousmonth volumes data is on same with comment for the sep month.

when we add previous month vs currenmont diff we get below output 

 


here current month is sep and as per calculations previous is Aug but comment we have it for Sep month only.

 

how we get data in one row in this case.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi chlimaye ,

     

    I suggest you to create a dimdate table with continuous date and then use datetime intelligence function: 

    PREVIOUSMONTH().
    submissionDate = ADDCOLUMNS(CALENDAR(DATE(2024,1,01),DATE(2024,12,31)),"Sub Month",FORMAT([Date],"YYYY-MM"),"YearMonth",YEAR([Date])*100 + MONTH([Date]))

    Relationship:

    Measures:

    M_Volume = CALCULATE(SUM('Table'[Volume]))
    Previous Volume = CALCULATE([M_Volume],PREVIOUSMONTH(submissionDate[Date]))
    Curre- PreviousMonth Diff = [M_Volume] - [Previous Volume]

     Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi chlimaye ,

         

        Please make sure there is continuous date in your dimdate table or datetime intelligence function will not work.

        I think your issue is based on your data model. You can download my attatchment file before and compare mine with yours.

        If this still couldn't help you solve your issue. Please share a sample file with us and show us a screenshot with the result you want. This will make it easier to find the solution.

         

        Best Regards,
        Rico Zhou

         

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