Custom weekly(shows week range) and MTD columns in a table

[MK85]

Hi,
Is there any possibility to show weekrange and MTD columns in PBI table. Any leads will be appriciated.

Thanks in advance

[v-linyulu-msft]

Hi,@MK85 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.First of all, regarding the calculated columns that you need to have a date range, my calculated columns are as follows:

weekrange = 
VAR start1=[Date]-WEEKDAY('main Table'[Date],2)+1
VAR end1=[Date]-WEEKDAY('main Table'[Date],2)+7
RETURN FORMAT(start1,"mm/dd")&"-"&FORMAT(end1,"mm/dd")

3. Secondly, regarding the measure you need to have MTD, my measure is as follows:

MTD = CALCULATE(SUM('main Table'[value]),DATESMTD('main Table'[Date]))

4.Here's my final result, which I hope meets your requirements.

5.Here are the relevant documents:

WEEKDAY function (DAX) - DAX | Microsoft Learn

DATESMTD function (DAX) - DAX | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[MK85]

@v-linyulu-msft many thanks for answer. I think, I did not explain my question properly. These are the column headers in PBI table visual. These headers will have values based on week and month. I am trying to accomplish this similar excel table in PBI.

Thanks in advance

[v-linyulu-msft]

Hi,@MK85

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the Calculated Columns I've created for your needs:

weekrange = 
VAR start1=[date]-WEEKDAY('MTD'[date],1)+1
VAR end1=[date]-WEEKDAY('MTD'[date],1)+7
RETURN FORMAT(start1,"mm/dd")&"-"&FORMAT(end1,"mm/dd")

In my calculation column, the week starts on Sunday, you can also modify the parameter to adjust it to start on Monday, here is the documentation for that:

WEEKDAY function (DAX) - DAX | Microsoft Learn
3. Create a matrix view and modify the settings as shown below:

4.Here's my final result, which I hope meets your requirements.

My pbix file is attached to this reply, so you can extract it in time if you need it.

Best Regards,

Leroy Lu

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

[MK85]

@v-linyulu-msftMany thanks for your effort and resonspe. Is there any possibility that MTD can be only sum of current month. I mean June, 01 onwards (doesn't include May numbers).
Thanks in advance

[v-linyulu-msft]

Hi,@MK85 

Thank you very much for your quick reply.

Regarding the issue you raised, my solution is as follows:
1.First create a calculation table:

mtd1 = 
VAR _summarize =
    SUMMARIZE (
        'MTD',
        [categories],[date],
        "value", CALCULATE(SUM('MTD'[value]),DATESMTD('MTD'[date])),
        "weekrange","MTD"
    )
RETURN
    UNION ( 'MTD', _summarize )

2.Here's my final result, which I hope meets your requirements.

3.Here are the relevant documents:
DATESMTD function (DAX) - DAX | Microsoft Learn

How to put the row and column subtotals in front o... - Microsoft Fabric Community
Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[MK85 · Accepted solution]

Thank you so much @v-linyulu-msft for your efforts. I found a simple solution with measures. As my data model is complex I cant afford adding new table to it.

[v-linyulu-msft]

Hi,@MK85 
We are happy to know that the issue has been resolved. We would appreciate it if you would be willing to share the solution with us to facilitate a solution for the same problem. Additionally, this would be beneficial to other community members as well.

Certainly, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu