Forum Discussion

Sachi08's avatar
Sachi08
Frequent Visitor
1 year ago
Solved

How to show data in below format?

Month

Total

Billed Count

Apr-24May-24Jun-24Jul-24Aug-24Sep-24Oct-24Nov-24Dec-24Jan-25Total paid Count
Apr-24131583179         3179
May-241318820001932        3932
Jun-241236619749681000       3942
Jul-241163519481012422715      4097
Aug-241180020711057447286554     4415
Sep-241177920911145503321219432    4711
Oct-241157921381164554346238168386   4994
Nov-241159921091154532366217169139283  4969
Dec-241153122041231583325248206172102331 5402
Jan-2575697423761631025956483227651670
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Sachi08,

    You can create a unconnected date table and use raw table date field as row, new table date as column to design a matrix visual.
    After these steps, you can create new measure formula to summary records based on current date:

    formula = 
VAR cRowDate =
    MAX ( 'Table'[Date] )
VAR cColumnDate =
    MAX ( NewTable[Date] )
RETURN
    IF (
        cRowDate <= cColumnDate,
        CALCULATE (
            SUM ( 'Table'[Bill] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Date] <= cRowDate && [Date] <= cColumnDate )
        )
    )


    Regards,

    Xiaoxin Sheng

4 Replies

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello Sachi08 

     

    i am not sure how your data looks like but here i create a simple example to create as your visual above.

    1. create a new calculated column which has exact same value as your date column.

    2. create a matrix visual and grab the values in matrix

    3. dont forget to tick Column Subtotal ON as you want to have a row sum value at the right side of matrix.

     

    Hope this will help.

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Sachi08,

    You can create a unconnected date table and use raw table date field as row, new table date as column to design a matrix visual.
    After these steps, you can create new measure formula to summary records based on current date:

    formula = 
VAR cRowDate =
    MAX ( 'Table'[Date] )
VAR cColumnDate =
    MAX ( NewTable[Date] )
RETURN
    IF (
        cRowDate <= cColumnDate,
        CALCULATE (
            SUM ( 'Table'[Bill] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Date] <= cRowDate && [Date] <= cColumnDate )
        )
    )


    Regards,

    Xiaoxin Sheng

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Sachi08 

     

    Not very straighforward. This will involve using a disconnected  table containing the column headers. Measures would have been enough if not for that extra column before the months and the custom total column name.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Sachi08 ,

    Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng